Macro help

[MikeJP]

I vba to check for the following:


empty cell
value
empty cell

If the above situation exists I need to go to the next x in a for to loop.

Thanks
Mike Piles

 

[Egress1]

Hi Mike,

How are the cells you're testing situated? Are they horizontal or vertical?

I personally would just use a label and an AND statement but then I'm pretty simple minded

If the cells are going across columns (horizontal) then something akin to

IF CELLS(MyRow,MyCol) = 0 AND CELLS(MyRow,MyCol+1) <> 0 AND CELLS(MyRow,MyCol+2)=0 THEN GOTO SKIP

Where MyRow and MyCol are variables and SKIP being a label you put just before the NEXT line in your loop.

If the cells are vertical ie in consecutive rows then leave the MyCol constant and increment the MyRow accordingly

For X = 1 to Whatever

' code blah blah

IF CELLS(MyRow,MyCol) = 0 AND CELLS(MyRow,MyCol+1) <> 0 AND CELLS(MyRow,MyCol+2)=0 THEN GOTO SKIP

' more code blah
SKIP:
NEXT X


Hope that helps
-Ken

 

[Yogi Anand]

Hi Mike:

Please post the code you are using and indicate where you are stuck or having problem going further.

 

[MikeJP]

Here is the code:

Sub INSERTSUM()


Dim x As Integer
Dim Var1 As Range
Dim Var2 As Range
Dim sText As String
Dim oper As String
Dim bottom As Long
Dim Btmcheck As Long




For x = 1 To 10

Set Var1 = Selection
Set Var2 = Var1.End(xlDown)
Let bottom = 65536
sText = Range(Var1, Var2).Address


oper = "=Sum(" & sText & ")"
Var2.Offset(1, 0).Value = oper
Selection.End(xlDown).Select
Let Btmcheck = Selection.Row
If Btmcheck = bottom Then
Exit For
End If

Selection.End(xlDown).Select
Let Btmcheck = Selection.Row
If Btmcheck = bottom Then
Exit For
End If


Next x
Range("a1").Select


End Sub


This code works fine on the following collumn of data:


222
333



333
333
444



555
666



But does not work properly on the followig collumn of data:


222
333



333




555
666


It will have a problem when there are not at least 2 numeric valus located in adjacent vertical cells. I either need it to skip one row entries or place the sum right under them, and continue on.

Thanks
Mike

 

[tusharm]

Use the FindFirstEmpty function in http://www.mrexcel.com/board2/viewtopic.php?p=210505#210505

Also, if you post code, please format it for readability. Paste the code into the text box, select the pasted code, and click the Code button near the top of the form.

{snip}
It will have a problem when there are not at least 2 numeric valus located in adjacent vertical cells. I either need it to skip one row entries or place the sum right under them, and continue on.

Thanks
Mike