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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Egress1

Active Member
Joined
Mar 2, 2003
Messages
420
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Mike:

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

MikeJP

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top