Has cell got a value in Excel? (IsNull?) 2

[biot023]

Hallo - I have to knock something together in Excel & wanted a date report every time a certain macro was run. To do this, I need to iterate thru the columns in a sheet until one is free, then fill it with a date (I have sample code below) from a field on another sheet.
Trouble is, it doesn't work. The Range just moves to A2 & stays there. I know there is going to be something astoundingly simple that solves this (like a variable for where you're at on a sheet or something), but I have a very thick skin, so please, anyone - just hit me with how stupid I'm being.

The code I spaghetti-ised whilst trying to do this runs:

Sub Macro6()

Dim rw As Long
Dim place As String
Dim val As String

rw = 2
place = "A" & rw

Range("A2&quot.Select
Application.CutCopyMode = False
'Selection.Copy 'COMMENTED OUT
val = Range("A2&quot.Text
Sheets("Record of Weeks&quot.Select
Range(place).Select
While (IsNull(Selection))
'While (Range(place) = Empty) 'COMMENTED OUT
rw = rw + 1
place = "A" & rw
Range(place).Select
Wend
Range(place).Select
'ActiveSheet.Paste 'COMMENTED OUT
Range(place).Value = val
Sheets("Font End&quot.Select
End Sub

Any & all advice gratefully appreciated.
Cheers, Douglas JL If it don't make you laugh, it ain't true.

 

[biot023]

I've just had somebody explain what an idiot I am to me.
The while loop should have read
While Not IsNull(...)
or
While ... <>&quot;&quot;

I didn't ask to be born dumb. If it don't make you laugh, it ain't true.

 

[acron]

does the followng do what you want :

rw = 2
place = "A" & rw
val = Range("A2").Text
Sheets("Record of Weeks").Range(place).End(xlDown).Offset(1, 0) = val

AC

 

[biot023]

It looks about right - I don't really know Excel, but I shall go & play.

Thanks alot.


DJL If it don't make you laugh, it ain't true.

 

[wadjssd]

biot023,

Hopefully this will do what you want far more simply:

Sub MyMacro()

val = Range(&quot;a2&quot.Value

Range(&quot;A2&quot.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = val
Sheets(&quot;Front End&quot.Select

End Sub

Cheeers,

wadjssd