cant figure out compile error: do without loop

[tzexu1610]

Hi guys,

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

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

 

[shaowu459]

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

 

[MARK858]

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

 

[tzexu1610]

thank you let me give it a shot now

 

[tzexu1610]

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?

Function Dec_places(value, round) As Long


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


End Function

 

[MARK858]

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

 

[tzexu1610]

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]

I'm afraid there is something basic you don't know. Please try this:

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

I'm afraid there is something basic you don't know. Please try this:

[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 :

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]

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.