PDA

View Full Version : Solved: Restart macro after blank cell



copyt
04-18-2012, 04:25 AM
Hi all, I have a code that can do some calculation for example

Column A

Blank
Blank
Blank
5
3
2
9
8
Blank
100
45
55
25
0
75
2.5
1.5
3.5

The code will find the matches between the first number after blank to the other numbers before next blank

the equation is "x = i + j" then change font in i and j cells to be bold

so the expected results would be

Blank
Blank
Blank
5
3
2
9
8
Blank
100
45
55
25
0
75
2.5
1.5
3.5

My problem is
1. when I run the code all numbers after number 5 will be taken into account to calculate for 5
2. How can I stop the code and go to next 5 if the code found a blank cell?

Any help would be appreciated.


Sub calculation()

Dim finalrow As Long, _
x As Long, _
j As Long, _
i As Long
Dim lngPasteRow As Long

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 4 To finalrow
For j = x + 1 To finalrow
For i = j + 1 To finalrow

If Cells(x, 1) <> "" And Cells(x - 1, 1) = "" And Cells(x + 1, 1) <> "" Then

If Cells(i, 1) + Cells(j, 1) = Cells(x, 1) Then


With Cells(i, 1).Font
.Bold = True
End With
With Cells(j, 1).Font
.Bold = True
End With

End If


ElseIf Cells(i, 1) Or Cells(j, 1) = "" Then

'Go to next X

End If

Next i
Next j
Next x
MsgBox "Finished"
End Sub

BrianMH
04-18-2012, 06:39 AM
Can you clarify what your requirements and expected results are?

I have corrected an error in your code below.

ElseIf Cells(i, 1) = "" Or Cells(j, 1) = "" Then

Bob Phillips
04-18-2012, 06:41 AM
Untested


Sub calculation()

Dim finalrow As Long, _
nextblank As Long, _
x As Long, _
j As Long, _
i As Long
Dim lngPasteRow As Long

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 4 To finalrow

For j = x + 1 To finalrow

nextblank = Cells(i, 1).End(xlDown).Row + 1
For i = j + 1 To nextblank - 1

If Cells(x, 1).Value <> "" And Cells(x - 1, 1).Value = "" And Cells(x + 1, 1).Value <> "" Then

If Cells(i, 1).Value + Cells(j, 1).Value = Cells(x, 1).Value Then

With Cells(i, 1).Font
.Bold = True
End With
With Cells(j, 1).Font
.Bold = True
End With
End If
ElseIf Cells(i, 1).Value = "" Or Cells(j, 1).Value = "" Then

'Go to next X
End If
Next i
Next j
Next x
MsgBox "Finished"
End Sub

copyt
04-18-2012, 03:35 PM
Sorry for my late reply,

@ BrianMH (http://www.vbaexpress.com/forum/member.php?u=20620), in the expected result
Blank
Blank
Blank
5
3
2
9
8
Blank
100
45
55
25
0
75
2.5
1.5
3.5

Number 3 and 2 are bold because 5 = 3+2
Number 45 and 55, 75 and 25 are bold because 100 = 45 + 55, 100 = 75 + 25

my problem is when I run the code, 1.5 and 3.5 are also calculated for 5. I would like to restart the code after blank cell to avoid that problem.


@xld (http://www.vbaexpress.com/forum/member.php?u=2139)
I got an error, Error 1004 Application defined or object defined error.

Thanks,

(http://www.mrexcel.com/forum/showthread.php?t=68044)

Aussiebear
04-18-2012, 04:53 PM
So you are basically chasing only whole numbers that sum up to the number and not decimal numbers?

copyt
04-18-2012, 05:35 PM
@Aussiebear (http://www.vbaexpress.com/forum/member.php?u=3907),

Thank you for your response. From the example I just wanted to show that 1.5 + 3.5 is equal to 5 (not 100) but I wanted only the combinations of numbers that're equal to 100 (first number after blank).

To be clearer I added additional numbers.

Blank
Blank
Blank
5
3
2
9
8
45
55
3.1
1.9
Blank
100
45
55
25
0
75
2.5
1.5
3.5
99
1

Bob Phillips
04-19-2012, 12:00 AM
@xld (http://www.vbaexpress.com/forum/member.php?u=2139)
I got an error, Error 1004 Application defined or object defined error.

Thanks,

(http://www.mrexcel.com/forum/showthread.php?t=68044)

On which code line and for which data row?

mancubus
04-19-2012, 02:24 AM
this line:

nextblank = Cells(i, 1).End(xlDown).Row + 1

i is currently 0.

Bob Phillips
04-19-2012, 04:56 AM
Thanks, t should have been


nextblank = Cells(j, 1).End(xlDown).Row + 1

copyt
04-19-2012, 08:06 AM
@ mancubus and xld,

Thank a lot for your kind helps.

mancubus
04-19-2012, 10:56 PM
you're wellcome but i did nothing. all credits must go to xld.

and thanks for marking the thread as solved.