Adding a new row will break my little VBA script

[doctortt]

I have these codes that will read all cells in row 3; if Excel finds the cell in that row with a value that matches (i.e.) 12345, it will pick up row name and add 100 to it. For example, the AG3 cell contains 12345, Excel will add 100 to it and make it AG103. (Then, the rest of my codes will use cell AG103 for something else.)

The problem here is when someone adds a new row onto the Excel sheet, this VBA no longer works; obviously, it's because the "100" is hard coded.

What can I do to resolve this problem?

[patel]

start attaching a sample file

[doctortt]

Hi, here you go. I apologize. 130, 135, 140 below are hard-coded; therefore, when someone adds a new row onto the worksheet, everything will stop working because the row reference will be wrong.

Dim LC As Long, cel As Range, dummy As Range
Dim AssF6 As String, Target1 As String, Target2 As String, Target3 As String
Dim x As String, y As String, z As String

'Find last column with value in it on row 3
LC = Cells(3, Columns.Count).End(xlToLeft).Column
AssF6 = Sheets("Assumptions").[F6].Value

'Search for Assumptions!F6 value from column 'A' to last column
For Each cel In Range(Cells(3, 1), Cells(3, LC))
If cel.Value = AssF6 Then
x = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 130)
Target1 = Target1 & " " & x

y = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 135)
Target2 = Target2 & " " & y

z = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 140)
Target3 = Target3 & " " & z

End If
Next cel

[magelan]

Hi, here you go. I apologize. 130, 135, 140 below are hard-coded; therefore, when someone adds a new row onto the worksheet, everything will stop working because the row reference will be wrong.

Dim LC As Long, cel As Range, dummy As Range
Dim AssF6 As String, Target1 As String, Target2 As String, Target3 As String
Dim x As String, y As String, z As String

'Find last column with value in it on row 3
LC = Cells(3, Columns.Count).End(xlToLeft).Column
AssF6 = Sheets("Assumptions").[F6].Value

'Search for Assumptions!F6 value from column 'A' to last column
For Each cel In Range(Cells(3, 1), Cells(3, LC))
If cel.Value = AssF6 Then
x = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 130)
Target1 = Target1 & " " & x

y = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 135)
Target2 = Target2 & " " & y

z = Replace(cel.Address(0, 0), Right(cel.Address(0, 0), 1), Right(cel.Address(0, 0), 1) + 140)
Target3 = Target3 & " " & z

End If
Next cel
also use code tags

code tags strip formatting and lay it out properly so we can see everything the right way. also makes it a bit smaller

Anyway.

Try adding something to the beginning of the row you want to identify it. Or if its already unique, you need code that will look through the rows to find the one you like.

for instance, i have a program where its a giant database, outlined in purple borders [fill purple, not conditionally purple]. I find the boundaries of my dynamic database by searching for purple borders so that if someone adds a column or a row, the borders are always there and it always finds the right boundary.