cant figure out compile error: do without loop

tzexu1610

New Member
Hi guys,

I can't figure out where is the do - loop error im having... my code is as follows

Code:
``````Sub round()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer

a = 2
b = -2
i = 10 'start row
j = -2 'start column

Do Until Cells(a, b).value = ""

If Cells(a, b).value = "Ag (Silver)" Then 'header start

i = 10
j = j + 4
b = b + 4

Do

If IsNumeric(Cells(i, j).value) = True Then '1 dec place

Call Dec_places(Cells(i, j).value, 1)

Else

i = i + 1

End If

Loop Until Cells(i, j + 1).value = ""

Else

Do

If IsNumeric(Cells(i, j).value) = True Then '0 dec place

Call Dec_places(Cells(i, j).value, 0)

Else

i = i + 1

End If

Loop Until Cells(i, j + 1).value = ""

End If

Application.ScreenUpdating = True
Application.DisplayStatusBar = True

End Sub``````

Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

shaowu459

Well-known Member
you have 3 pairs of DO-LOOP, the first DO is [Do Until Cells(a, b).Value = ""], but don't have a LOOP.

Last edited:

MARK858

MrExcel MVP
You can't have a negative column which is what b is in Cells(a, b).

tzexu1610

New Member
thank you let me give it a shot now

tzexu1610

New Member

you have 3 pairs of DO-LOOP, the first DO is [Do Until Cells(a, b).Value = ""], but don't have a LOOP.

hi, i changed my code and fixed that problem, but now i get another error when trying to use that function. could you please help me correct this?

Code:
``````Function Dec_places(value, round) As Long

If IsNumeric(value) = True Then
value = round(value, round)

Else

End If

End Function``````

MARK858

MrExcel MVP
So what does the error say and what line is highlighted.

tzexu1610

New Member

So what does the error say and what line is highlighted.

it says mismatch, and "value = round(value, round)" this line is highlighted yellow. I put it underneath my other code, below the end sub. I thought doing so will "call the function" like calling a sub. Im still new to applying functions into macros so im unsure how to go about this.

shaowu459

Well-known Member
I'm afraid there is something basic you don't know. Please try this:
Code:
``````[COLOR=#333333]Function Dec_places(value, round) As Long[/COLOR]

If IsNumeric(value) = True Then
[FONT=Verdana]Dec_places[/FONT][FONT=Verdana] = round(value, round)[/FONT]
Else

End If

[COLOR=#333333]End Function[/COLOR]``````

Enter "=Dec_places(X,X)" in one cell and press enter to finish will return what you want(assuming you code is right), just like =SUM(A1:A2).

tzexu1610

New Member
I'm afraid there is something basic you don't know. Please try this:
Code:
``````[COLOR=#333333]Function Dec_places(value, round) As Long[/COLOR]

If IsNumeric(value) = True Then
[FONT=Verdana]Dec_places[/FONT][FONT=Verdana] = round(value, round)[/FONT]
Else

End If

[COLOR=#333333]End Function[/COLOR]``````

Enter "=Dec_places(X,X)" in one cell and press enter to finish will return what you want(assuming you code is right), just like =SUM(A1:A2).

Sorry still getting runtime error 13, mismatch. Line "Dec_places = round(value, round is highlighted). My whole code goes like this :

Rich (BB code):
``````Sub round()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer

a = 2
b = -2
i = 10 'start row
j = -2 'start column

j = j + 4
b = b + 4

Do Until Cells(a, b).value = ""

If Cells(a, b).value = "Ag (Silver)" Then 'header start

i = 10
j = j + 4
b = b + 4

Do

If IsNumeric(Cells(i, j).value) = True Then '1 dec place

Call Dec_places(Cells(i, j).value, 1)

Else

i = i + 1

End If

Loop Until Cells(i, 1).value = ""

Else

Do

If IsNumeric(Cells(i, j).value) = True Then '0 dec place

Call Dec_places(Cells(i, j).value, 0)

Else

i = i + 1

End If

Loop Until Cells(i, 1).value = ""

End If

Loop

Application.ScreenUpdating = True
Application.DisplayStatusBar = True

End Sub

Function Dec_places(value, round) As Long

If IsNumeric(value) = True Then
Dec_places = round(value, round)

Else

End If

End Function``````

If it helps, I have columns of data , mixed with integers , and stuff like <1, <2 etc. So im trying to round off those that are numbers, and skip those that are <1 / <2 / text

shaowu459

Well-known Member
I'm afraid you may not able to call the sub_Round() in cell, the parameter for a User-Defined Function may not be a sub. If my understanding is wrong, please update me, thanks.

Last edited:

Replies
12
Views
122
Replies
0
Views
39
Replies
2
Views
85
Replies
9
Views
136
Replies
3
Views
101

1,136,763
Messages
5,677,606
Members
419,706
Latest member
lydytunes13

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.

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

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