VBABeginer_Chappers
New Member
- Joined
- Mar 8, 2018
- Messages
- 10
Hi
I have been trying to write some code which checks if a cell is empty, if the cell is empty it moves the data from that row and the next 2 columns down a row. The problem I have is that the second column contains the address of a linked cell, hence I need to check if the address is still correct and change it if required.
e.g
<tbody>
</tbody>
On the example above If I was to check cell S4, it would move Range S4:S9 down a row, The reference "$S$9" in Cell T7 would need to change to $S$10 however the reference "$S$2" at T5 would not, The number in column U is to pair the Jump with a Jump From.
Line
"JumpNumberLcn = Range(.Range("U2:U" & h), .Range("U" & j:"U1000")).Find(JumpNumber)"
doesn't Work
Many Thanks
I have been trying to write some code which checks if a cell is empty, if the cell is empty it moves the data from that row and the next 2 columns down a row. The problem I have is that the second column contains the address of a linked cell, hence I need to check if the address is still correct and change it if required.
e.g
S | T | U | |
2 | Inbox | ||
3 | Jump From | 2 | |
4 | Copy To | ||
5 | Jump | $S$2 | 2 |
6 | Outbox | ||
7 | Jump | $S$9 | 3 |
8 | Add | $C$4 | |
9 | Jump From | 3 | |
10 |
<tbody>
</tbody>
On the example above If I was to check cell S4, it would move Range S4:S9 down a row, The reference "$S$9" in Cell T7 would need to change to $S$10 however the reference "$S$2" at T5 would not, The number in column U is to pair the Jump with a Jump From.
Code:
Sub CheckIfEmpty()
Dim j, h As Integer
Dim JumpNumber As Integer
Dim JumpNumberLcn As String
If Not IsEmpty(ActiveCell.Value) Then
Selection.Insert Shift:=xlDown
Selection.Offset(0, 1).Insert Shift:=xlDown
Selection.Offset(0, 2).Insert Shift:=xlDown
Selection.Offset(0, -1).Insert Shift:=xlDown
For i = x To 1000
j = i + 1
h = i - 1
If Range("S" & i).Value = "Jump" Then
JumpNumber = Range("U" & i)
JumpNumberLcn = Range(.Range("U2:U" & h), .Range("U" & j:"U1000")).Find(JumpNumber)
Range("T" & i).Value = JumpNumberLcn
Else
End If
Next i
Else
Exit Sub
End If
End Sub
Line
"JumpNumberLcn = Range(.Range("U2:U" & h), .Range("U" & j:"U1000")).Find(JumpNumber)"
doesn't Work
Many Thanks