Run Macro from cell change THEN select adjacent cell
 

FIRST, thanks in advance to All that respond...

In this spreadsheet, I have text in B2:B30. I would like to run a macro
once I edit one of the cells - the Macro is a randomizer formula, in which I
would like the result to land into F2:F30.

EXAMPLE

If I edit B7, once i get out of that cell, I would want the randomizer
result to land in F7...not the entire range.

Hopefully that makes sense...I have limited knowledge of VB, but am playing
with it
Cheers

Run Macro from cell change THEN select adjacent cell
 

Right click on your sheet tab and select view code. Then paste this macro
into the code sheet that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then
Target(1, 5).Value = "Randomizer"
End If
Application.EnableEvents = True
End Sub


You will need to put your code to calculate your "Randomizer" value inside
of the IF statement. Right now, I just assign the word "Randomizer" to the
corresponding cell in column F.



"mslabbe" wrote:

FIRST, thanks in advance to All that respond...

In this spreadsheet, I have text in B2:B30. I would like to run a macro
once I edit one of the cells - the Macro is a randomizer formula, in which I
would like the result to land into F2:F30.

EXAMPLE

If I edit B7, once i get out of that cell, I would want the randomizer
result to land in F7...not the entire range.

Hopefully that makes sense...I have limited knowledge of VB, but am playing
with it
Cheers

Run Macro from cell change THEN select adjacent cell
 

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("B2:B30")).Address = "$B$2:$B$30" Then
Target.Offset(0, 4).Value = Rnd()
End If
End Sub

This would go in the code module for the sheet in question. Just
replace "Rnd()" by your VBA formula or function.

Hope that helps

-John Coleman

mslabbe wrote:
FIRST, thanks in advance to All that respond...

In this spreadsheet, I have text in B2:B30. I would like to run a macro
once I edit one of the cells - the Macro is a randomizer formula, in which I
would like the result to land into F2:F30.

EXAMPLE

If I edit B7, once i get out of that cell, I would want the randomizer
result to land in F7...not the entire range.

Hopefully that makes sense...I have limited knowledge of VB, but am playing
with it
Cheers

Run Macro from cell change THEN select adjacent cell
 

That works perfectly!!!
Thanks

"JMB" wrote:

Right click on your sheet tab and select view code. Then paste this macro
into the code sheet that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then
Target(1, 5).Value = "Randomizer"
End If
Application.EnableEvents = True
End Sub


You will need to put your code to calculate your "Randomizer" value inside
of the IF statement. Right now, I just assign the word "Randomizer" to the
corresponding cell in column F.



"mslabbe" wrote:

FIRST, thanks in advance to All that respond...

In this spreadsheet, I have text in B2:B30. I would like to run a macro
once I edit one of the cells - the Macro is a randomizer formula, in which I
would like the result to land into F2:F30.

EXAMPLE

If I edit B7, once i get out of that cell, I would want the randomizer
result to land in F7...not the entire range.

Hopefully that makes sense...I have limited knowledge of VB, but am playing
with it
Cheers

Run Macro from cell change THEN select adjacent cell
 

You are welcome.

"mslabbe" wrote:

That works perfectly!!!
Thanks

"JMB" wrote:

Right click on your sheet tab and select view code. Then paste this macro
into the code sheet that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B2:B30")) Is Nothing Then
Target(1, 5).Value = "Randomizer"
End If
Application.EnableEvents = True
End Sub


You will need to put your code to calculate your "Randomizer" value inside
of the IF statement. Right now, I just assign the word "Randomizer" to the
corresponding cell in column F.



"mslabbe" wrote:

FIRST, thanks in advance to All that respond...

In this spreadsheet, I have text in B2:B30. I would like to run a macro
once I edit one of the cells - the Macro is a randomizer formula, in which I
would like the result to land into F2:F30.

EXAMPLE

If I edit B7, once i get out of that cell, I would want the randomizer
result to land in F7...not the entire range.

Hopefully that makes sense...I have limited knowledge of VB, but am playing
with it
Cheers