creating a macro with IsEmpty

  • Thread starter Thread starter mkness
  • Start date Start date
M

mkness

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
 
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
 
Back
Top