Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Help with IsEmpty | Excel Programming | |||
isempty | Excel Programming | |||
vba: isempty | Excel Programming |