Using Offset in VBA

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
 
D

Dave Peterson

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
 
Top