Macros

J

John

Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg A24,
run the macro and have it place data in that row and I cant work out how to
do that.

Any help much apprecaited

Cheers
John
 
B

Bob Phillips

An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)
 
J

JimMay

Bob:
Can the below portion of your code be modified using A1 Notation, versus
the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"




An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

John said:
Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg A24,
run the macro and have it place data in that row and I cant work out how to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
 
C

Chip Pearson

Excel will automatically convert the formula to A1 notation when
it inserts the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

JimMay said:
Bob:
Can the below portion of your code be modified using A1
Notation, versus the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"




An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column
H"
Me.Cells(.Row, "M").Value = "something in column
M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing
direct)

John said:
Hi - New to macros so forgive what is probably a silly
question.

I am experimenting with developing a macro and have used one
as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of
any row,eg A24,
run the macro and have it place data in that row and I cant
work out how to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

As Chip says, it will hit the spreadsheet as A1 if A1 style is active, but
it would be done in VBA as

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

JimMay said:
Bob:
Can the below portion of your code be modified using A1 Notation, versus
the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"




An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

John said:
Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg A24,
run the macro and have it place data in that row and I cant work out
Click to expand...
how
to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
J

JimMay

Thanks Chip;
I did notice that it had been converted (in the sheet-cell).
I've had "a thing" against R1C1 from the beginning..
(Maybe due to my dyslectia (sp?)) LOL It realy screws up
my small mind.
So I was interested in knowing how to
write it out (in A1 notation) in the code.
Tried for 10-15 minutes, but couldn't get it going.
Jim


Excel will automatically convert the formula to A1 notation when
it inserts the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

JimMay said:
Bob:
Can the below portion of your code be modified using A1
Notation, versus the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"




An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column
H"
Me.Cells(.Row, "M").Value = "something in column
M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing
direct)

Hi - New to macros so forgive what is probably a silly
question.

I am experimenting with developing a macro and have used one
as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of
any row,eg A24,
run the macro and have it place data in that row and I cant
work out how
to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Just a typo...

You forgot to change .formular1c1:

Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

Bob said:
As Chip says, it will hit the spreadsheet as A1 if A1 style is active, but
it would be done in VBA as

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

JimMay said:
Bob:
Can the below portion of your code be modified using A1 Notation, versus
the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"




An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg A24,
run the macro and have it place data in that row and I cant work out how
to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
J

JimMay

Thanks for:
Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

Had to modify it to:

Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ",4)=""some"",""yes"",""no"")" ' include the ,4
(the number of characters)

And it works great !!
Thanks,
Jim


Just a typo...

You forgot to change .formular1c1:

Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

Bob said:
As Chip says, it will hit the spreadsheet as A1 if A1 style is active, but
it would be done in VBA as

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

JimMay said:
Bob:
Can the below portion of your code be modified using A1 Notation, versus
the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"





An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg A24,
run the macro and have it place data in that row and I cant work out how
to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Worksheet function LEFT defaults to one character Jim, unlike the VBA
version.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

JimMay said:
Thanks for:
Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

Had to modify it to:

Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ",4)=""some"",""yes"",""no"")" ' include the ,4
(the number of characters)

And it works great !!
Thanks,
Jim


Just a typo...

You forgot to change .formular1c1:

Me.Cells(.Row, "N").Formula =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

Bob said:
As Chip says, it will hit the spreadsheet as A1 if A1 style is active, but
it would be done in VBA as

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(M" & .row & ")=""some"",""yes"",""no"")"

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

Bob:
Can the below portion of your code be modified using A1 Notation, versus
the R1C1? If so, could you demonstrate?
TIA,
Jim

Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"





An example

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Column = 1 Then
Me.Cells(.Row, "H").Value = "something in column H"
Me.Cells(.Row, "M").Value = "something in column M"
Me.Cells(.Row, "N").FormulaR1C1 =
"=IF(LEFT(RC[-1],4)=""some"",""yes"",""no"")"
End If
End With
End Sub


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

Hi - New to macros so forgive what is probably a silly question.

I am experimenting with developing a macro and have used one as a
template
which places data in A3:F3.

What I am trying to do is to click in the first coilumn of any row,eg
A24,
run the macro and have it place data in that row and I cant work out
how
to
do that.

Any help much apprecaited

Cheers
John
Click to expand...
Click to expand...
Click to expand...
 
Top