Macro help

MikeJP

Board Regular
Joined
Mar 10, 2003
Messages
51
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
Hi Mike:

Please post the code you are using and indicate where you are stuck or having problem going further.
 
Upvote 0
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
 
Upvote 0
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.

MikeJP said:
{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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top