Hi,

I have a piece of code that looks at an Excel spreadsheet, copies the cells
data, and puts it in another Excel sheet.

The first piece of data will always be there. But in some cases, subsequent
fields may be blank. So I want to put in an If statement that says if
subsequent values are blank, just fill them in with the first value. Here is
what I have:

testExpected = .Cells(currentRow, 6).Value
*******
If .Cells.Value is Null then
blah blah
End If
*******
Can someone help me with the code inside the asterisks please.

Can you try to use the word 'Empty' instead of 'Null'?
So...

If .Cells.Value = Empty Then
blah
End If




"Catherine Jackson" schreef in
bericht ...
Hi,

I have a piece of code that looks at an Excel spreadsheet, copies the
cells
data, and puts it in another Excel sheet.

The first piece of data will always be there. But in some cases,
subsequent
fields may be blank. So I want to put in an If statement that says if
subsequent values are blank, just fill them in with the first value. Here
is
what I have:

testExpected = .Cells(currentRow, 6).Value
*******
If .Cells.Value is Null then
blah blah
End If
*******
Can someone help me with the code inside the asterisks please.

I tried this with a worksheet and it identified the empty cells correctly.
This may or may not be what you are looking for. A blank cell can be
identified as an empty string "".

Jeff

Sub JEFF()

Dim ROW As Long
Dim TEMP As String

ROW = 1
Do While ROW < 10
TEMP = ActiveSheet.Cells(ROW, 1).Value
If TEMP = "" Then
Cells(ROW, 2).Value = "BLANK"
Else
Cells(ROW, 2).Value = "NOT BLANK"
End If
ROW = ROW + 1
Loop
End Sub

"Catherine Jackson" wrote:

Hi,

I have a piece of code that looks at an Excel spreadsheet, copies the cells
data, and puts it in another Excel sheet.

The first piece of data will always be there. But in some cases, subsequent
fields may be blank. So I want to put in an If statement that says if
subsequent values are blank, just fill them in with the first value. Here is
what I have:

testExpected = .Cells(currentRow, 6).Value
*******
If .Cells.Value is Null then
blah blah
End If
*******
Can someone help me with the code inside the asterisks please.

If you dont want to use a loop statement you can use,

ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s).Value = "First
Value"

Now this will put that first value in all blank cells for the entire
used range of the active sheet. If you need to be more specific to a
column or row, just change the first part.


--
jtp
------------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
View this thread: http://www.excelforum.com/showthread...hreadid=569543