cant figure out compile error: do without loop

tzexu1610

New Member
Joined
Jan 15, 2018
Messages
15
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


'heading


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

Thanks in advance
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
You can't have a negative column which is what b is in Cells(a, b).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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


'heading


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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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