creating a macro with IsEmpty

[mkness]

I'm new to posting threads and also new to writing in VBA. I've been only looking at macros that work and tweaking them to be beneficial to me. What i'm trying to do is in Excel, manually highlight qty's on a specific row, run the macro and the macro will determine if that cell is empty, go to the next column of the highlighted row. if there is a qty in the cell, it'll copy and paste it into a program with a associated store#. I've gotten the macro to work so that it'll copy and paste every cell that's highlighted but can't get it to skip the cells that are empty. I'm thinking I'm to use a If, then, else formula, but I'm not getting the structure correct and I'm not familiar with the IsEmpty function....can any help? Thanks


Sub Copy_store_plus_data2()
Dim OriginalRow As Integer
Dim OriginalCol As Integer
Dim NumAreas As Integer
Dim NumRowsInSelection As Integer
Dim i As Integer
Dim j As Integer
Dim k
Dim single_row As Range
Dim single_column As Range


OriginalRow = ActiveCell.row
OriginalCol = ActiveCell.Column
NumAreas = Selection.Areas.Count

AppActivate "Program"

Send_Keys_Then_Wait "{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{right}{tab}"

For Each single_column In Selection.Columns

If IsEmpty(OriginalRow, single_column.Column) Then Next single_column


Else

Send_Keys_Then_Wait "{f5}" & "S" & "{tab}" _
& "{f5}" & Cells(5, single_column.Column).Text & "{tab}" _
& "{f5}" & Cells(OriginalRow, single_column.Column).Text _
& "{down}"

End If

Next single_column



Cells(OriginalRow, OriginalCol).Activate

Set single_row = Nothing
Set single_column = Nothing



End Sub

[Tom Hutchins]

Instead of IsEmpty, check the length of the value in the cell:

If Len(Cells(OriginalRow, single_column.Column).Value) = 0 Then

This is the best and fastest way to test if a cell is empty.

Hope this helps,

Hutch

"mkness" wrote:

>
> I'm new to posting threads and also new to writing in VBA. I've been
> only looking at macros that work and tweaking them to be beneficial to
> me. What i'm trying to do is in Excel, manually highlight qty's on a
> specific row, run the macro and the macro will determine if that cell
> is empty, go to the next column of the highlighted row. if there is a
> qty in the cell, it'll copy and paste it into a program with a
> associated store#. I've gotten the macro to work so that it'll copy
> and paste every cell that's highlighted but can't get it to skip the
> cells that are empty. I'm thinking I'm to use a If, then, else
> formula, but I'm not getting the structure correct and I'm not familiar
> with the IsEmpty function....can any help? Thanks
>
>
> Sub Copy_store_plus_data2()
> Dim OriginalRow As Integer
> Dim OriginalCol As Integer
> Dim NumAreas As Integer
> Dim NumRowsInSelection As Integer
> Dim i As Integer
> Dim j As Integer
> Dim k
> Dim single_row As Range
> Dim single_column As Range
>
>
> OriginalRow = ActiveCell.row
> OriginalCol = ActiveCell.Column
> NumAreas = Selection.Areas.Count
>
> AppActivate "Program"
>
> Send_Keys_Then_Wait
> "{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{tab}{right}{tab}"
>
> For Each single_column In Selection.Columns
>
> If IsEmpty(OriginalRow, single_column.Column) Then Next
> single_column
>
>
> Else
>
> Send_Keys_Then_Wait "{f5}" & "S" & "{tab}" _
> & "{f5}" & Cells(5, single_column.Column).Text &
> "{tab}" _
> & "{f5}" & Cells(OriginalRow,
> single_column.Column).Text _
> & "{down}"
>
> End If
>
> Next single_column
>
>
>
> Cells(OriginalRow, OriginalCol).Activate
>
> Set single_row = Nothing
> Set single_column = Nothing
>
>
>
> End Sub
>
>
> --
> mkness
> ------------------------------------------------------------------------
> mkness's Profile: http://www.excelforum.com/member.php...o&userid=36452
> View this thread: http://www.excelforum.com/showthread...hreadid=562208
>
>