Using Offset in VBA

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

I am quite new to VBA.

I have a range O5:O21 called "thisarea" which is not always full.

Using

Sub test()

Dim rascal As Range

For Each rascal In Range("thisarea")
With Sheets("front page")
.Range("a2").Value = rascal.Value
.Calculate
MsgBox rascal.Value



End With
Next rascal

End Sub

I would like this to return only 'full cells' (the cells will always
have a formular in them)

I know offset will work but I am having a bad day and can't work it
out


Help please

Matthew
 
I'm not sure how offset fits in here, but if you wanted to look at the cells in
ThisArea that don't evaluate to "" (looking empty):

Sub test()
Dim rascal As Range

For Each rascal In Range("thisarea").cells
With Sheets("front page")
if rascal.value = "" then
'skip it
else
.Range("a2").Value = rascal.Value
.Calculate
MsgBox rascal.Value
end if
End With
Next rascal
End Sub

If you wanted to avoid the cells that are really empty--no values, no formulas:

Sub test()
Dim rascal As Range

For Each rascal In Range("thisarea").cells
With Sheets("front page")
if isempty(rascal.value) then
'skip it
else
.Range("a2").Value = rascal.Value
.Calculate
MsgBox rascal.Value
end if
End With
Next rascal
End Sub
 
Back
Top