Solved: Restart macro after blank cell

[copyt]

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.


[VBA]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


[/VBA]

[BrianMH]

Can you clarify what your requirements and expected results are?

I have corrected an error in your code below.

[VBA]ElseIf Cells(i, 1) = "" Or Cells(j, 1) = "" Then [/VBA]

[Bob Phillips]

Untested

[vba]
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[/vba]

[copyt]

Sorry for my late reply,

@ BrianMH, 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
I got an error, Error 1004 Application defined or object defined error.

Thanks,

[Aussiebear]

So you are basically chasing only whole numbers that sum up to the number and not decimal numbers?

[copyt]

@Aussiebear,

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]

@xld
I got an error, Error 1004 Application defined or object defined error.

Thanks,

On which code line and for which data row?

[mancubus]

this line:
[VBA]
nextblank = Cells(i, 1).End(xlDown).Row + 1
[/VBA]
i is currently 0.

[Bob Phillips]

Thanks, t should have been

[vba]
nextblank = Cells(j, 1).End(xlDown).Row + 1 [/vba]

[copyt]

@ mancubus and xld,

Thank a lot for your kind helps.

[mancubus]

you're wellcome but i did nothing. all credits must go to xld.

and thanks for marking the thread as solved.