Using a variable to select a range excluding one cell.

[VBABeginer_Chappers]

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

	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.

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

 

[Norie]

Which column are you checking for empty cells?

 

[VBABeginer_Chappers]

Hi Apologies I'm checking Column S. The sub is part of a much bigger program which works down column S, and calls this sub.

 

[VBABeginer_Chappers]

The main thing I am struggling with is I want to select range A1:A1000 but not row "i" where i is the variable that changes in my loop