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

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
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You can't have a negative column which is what b is in Cells(a, b).
 

tzexu1610

New Member
Joined
Jan 15, 2018
Messages
15

ADVERTISEMENT

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
Joined
Nov 12, 2010
Messages
14,141
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So what does the error say and what line is highlighted.
 

tzexu1610

New Member
Joined
Jan 15, 2018
Messages
15

ADVERTISEMENT

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
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2018
Messages
15
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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

Threads
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.
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