Is there a way?

[Kevin W]

I'm a beginner with macros but am quickly realizing how they can make
everything easier.

I'd like to create a macro that Replaces all cells that have $J$2 in the
formula with $J$3. I have a cell (we'll call it H1) that sums up hundreds of
other cells (sum normally will equal 0). After Find/Replacing $J$2 with
$J$3, I want the macro to look at H1. If H1 still equals 0, then I want to
replace $J$3 with $J$4. If H1 still equals 0, I want to replace $J$4 with
$J$5. I want everything to stop when H1 equals anything other than 0.

Then, I would like to run it again - but starting from the next one where I
left off (so I don't want to start at $J$2 again). I'm not sure if this is
possible --maybe a prompt asking what row to start with?

 

[JLatham]

Take a look at using INDIRECT(). Then you don't have to hunt down all of the
formulas that have $J$2 in them and change that to $J$3, ect. and also have
to "remember" each time what the last row reference to $J$# was so you can
find it to change again.

Consider these trivial examples
G1 = "J2:J99"
Then in another cell a formula like =SUM(INDIRECT(G1))
gives the same result as =SUM(J2:J99)

and if G1 = "J2" then
in another cell a formula like =SUM((INDIRECT(G1)+K4-L3)/V5)
gives the same result as =SUM((J2+K4-L3)/V5)

So you could have code that just changes the value in G1 (or whatever cell
you choose to store the revised $J$# address in) such as this one which shows
how to set up for either an indirect reference to $J$#:$J$## (where # is a
start row number and ## is an ending row number), or just to set up a $J$#
entry in G1.

Sub ChangeIndirectReferenceValue()
Dim startRow As Long
Dim lastRow As Long

startRow = InputBox("Enter Starting Row (zero to quit)", _
"Start Row", 0)
If startRow < 1 Then
Exit Sub ' invalid entry
End If
'get the last used row number in column J
lastRow = Range("J" & Rows.Count).End(xlUp).Row
'or you can set lastRow to a fixed value as:
' lastRow = 99 ' to stop at row 99
'initialize the formula to the starting row
'we will now put our range into a cell that is
'referenced in the formulas via indirect
'add some safety valves to the test for H1=0
Do While Range("H1").Value = 0 And _
startRow <= lastRow And _
startRow < Rows.Count
'this would give an entry like J2:J99 in G1
Range("G1") = "$J$" & startRow & ":$J$" & lastRow

'or you can do it this way if better for your needs
'this would just keep the change to show J# in G1
Range("G1") = "$J$" & startRow
'in either case you need to increment the row pointer
' add 1 to get J3, J4, etc for as long as H1 =0
' or until
startRow = startRow + 1 ' add 1 to get J3, J4, etc
Loop

End Sub

 

[Sean Timmons]

I'll leave to you to create an input box loop around this, but here's the
initial search...


Dim i, j As Integer
Dim pkfrom, pkto, Result As String
i = 3
j = 4
pkfrom = "$J$" & i
pkto = "$J$" & j
While Range("H1").Value = 0
Cells.Replace What:=pkfrom, Replacement:=pkto, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i = i + 1
j = j + 1
pkfrom = "$J$" & i
pkto = "$J$" & j
Wend

 

[Kevin W]

Works very well, I like that you can see where you left off by the contents
of G1. Press my luck: is there a way to automatically color in the cell that
is indicated by G1? This would = less strain on the eyes.

 

[JLatham]

Yes and no. Again, because I don't yet know how many sheets are involved it
is difficult to give a good, reliable answer. But I think if you rewrite the
Do Loop portion of it like this, then it'll work. Not positive that it won't
leave a shaded cell on the worksheet when it's all done though. Haven't
tested it at all.

Do While Range("H1").Value = 0 And _
startRow <= lastRow And _
startRow < Rows.Count
'reset cell color of currently referenced cell to RED
Range(Range("G1").value).Interior.ColorIndex = xlNone

'this would give an entry like J2:J99 in G1
Range("G1") = "$J$" & startRow & ":$J$" & lastRow

'or you can do it this way if better for your needs
'this would just keep the change to show J# in G1
Range("G1") = "$J$" & startRow
'in either case you need to increment the row pointer
' add 1 to get J3, J4, etc for as long as H1 =0

'set cell color of currently referenced cell to RED
Range(Range("G1").value).Interior.ColorIndex = 3
startRow = startRow + 1 ' add 1 to get J3, J4, etc
Loop