creating a macro with IsEmpty
 

I'm new to posting threads and also new to writing in VBA. I've bee
only looking at macros that work and tweaking them to be beneficial t
me. What i'm trying to do is in Excel, manually highlight qty's on
specific row, run the macro and the macro will determine if that cel
is empty, go to the next column of the highlighted row. if there is
qty in the cell, it'll copy and paste it into a program with
associated store#. I've gotten the macro to work so that it'll cop
and paste every cell that's highlighted but can't get it to skip th
cells that are empty. I'm thinking I'm to use a If, then, els
formula, but I'm not getting the structure correct and I'm not familia
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_Wai
"{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 Nex
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 Su

--
mknes
-----------------------------------------------------------------------
mkness's Profile: http://www.excelforum.com/member.php...fo&userid=3645
View this thread: http://www.excelforum.com/showthread.php?threadid=56220

creating a macro with IsEmpty
 

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