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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Inbox[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jump From[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Copy To[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jump[/TD]
[TD]$S$2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Outbox[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jump[/TD]
[TD]$S$9[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Add[/TD]
[TD]$C$4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jump From[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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