Macros

  • Thread starter Thread starter John
  • Start date Start date
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
 
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)
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combining row values into one cell while filter is active 6
moving rows to another sheet 3
Using Drop Down Menu for Macro 6
Moving a bunch of data 1
Macro to add Multiple rows in same sheet 1
How to record a macro within a macro 9
Macro to insert a line when a value changes in a range 1
Number series at end of column macro? 4

Back
Top