Getting Debug on VBA from cells format?

[zone709]

Hi I get a break down below. What I do is some matching if values match.

On a normal sheet it works fine with the numbers, but I am trying to run it off of numbers that formulas are pulling in.

The thing is im not sure if the break is telling me this or its the cell formats

Sub Run()   
    Dim myFndCll As Range
    Dim myNumID As String
    Dim myAmount As Double
    Dim lrow As Long
    lrow = Cells(Rows.count, "A").End(xlUp).Row
    
    For LngLp = 2 To lrow
    myNumID = Cells(LngLp, "A") 'Get New Value
  [COLOR=#b22222]  myAmount = Cells(LngLp, "A").Offset(, 1)  <------Breaks here[/COLOR]
    
    Set myFndCll = Columns(3).Find(What:=myNumID, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole)
                    
    If myFndCll Is Nothing Then
       'Value doesn't exist in column C so highlight value in column A
          Cells(LngLp, "A").Interior.ColorIndex = 4
    Else
        If myAmount <> myFndCll.Offset(, 1) Then
            myFndCll.Offset(, 1).Interior.ColorIndex = 6
            Cells(LngLp, "A").Offset(, 1).Interior.ColorIndex = 6
        End If
    End If
    Next LngLp

End Sub

 

[Joe4]

Are all your values in column B numeric?
If not, you will have trouble, as you have declared myAmount to be "Double" (so it will not accept text).

If that is not the issue, please tell us the exact error message you are seeing.

 

[zone709]

im not sure i have that column all in general and those formulas are also pulling numbers from general


Run-time error '13':

Type mismatch

 

[Joe4]

I added some error handling to your code. When you run it, it should tell you exactly which cell is giving you issues. What is the value in that cell?

Sub MyRun()

    Dim myFndCll As Range
    Dim myNumID As String
    Dim myAmount As Double
    Dim lrow As Long
    lrow = Cells(Rows.Count, "A").End(xlUp).Row

    On Error GoTo err_chk
    For LngLp = 2 To lrow
        myNumID = Cells(LngLp, "A") 'Get New Value
        myAmount = Cells(LngLp, "A").Offset(, 1)
    
        Set myFndCll = Columns(3).Find(What:=myNumID, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole)
                    
        If myFndCll Is Nothing Then
       'Value doesn't exist in column C so highlight value in column A
            Cells(LngLp, "A").Interior.ColorIndex = 4
        Else
            If myAmount <> myFndCll.Offset(, 1) Then
                myFndCll.Offset(, 1).Interior.ColorIndex = 6
                Cells(LngLp, "A").Offset(, 1).Interior.ColorIndex = 6
            End If
        End If
    Next LngLp
    On Error GoTo 0

    Exit Sub
    
err_chk:
    If Err.Number = 13 Then
        MsgBox "Error with cell: " & Cells(LngLp, "A").Offset(, 1).Address
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[zone709]

ok after running your code. The error in the B columns come up if the cell is empty. So what i did is add a number for the formula to pull in and ran it again. It then looks for the next empty cell in B and then tells you what it is.

My orginal code if i have numbers in the first 4 columns and if a number was empty in B. It left it alone. So not sure why not im getting a debug for column B. I just ran my code on a new sheet with just number as you can see row 4 doesntmatch so it goes yellow. B5 empty but no debug. Just trying to get this to work on my other sheet where the formuals are pulling the nmbers in.


Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

23​

[/TD]
[TD]

6​

[/TD]
[TD]

23​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

52​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD]

52​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]

6​

[/TD]
[TD]

23​

[/TD]
[TD]

6​

[/TD]
[TD]

23​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


Have a feeling its the formatting of the cells dont know why

 

[Joe4]

Are those blank really blanks, or are they something like a single space?
It would return that error if you actually have spaces instead of empty cells, as you cannot set the variable "myAmount" equal to a space since you have declared it as a number (Double) in your code.

See if making this change to your original code fixes it:

myAmount = [COLOR=#ff0000]Val([/COLOR]Cells(LngLp, "A").Offset(, 1)[COLOR=#ff0000])[/COLOR]

 

[zone709]

ok now its working thanks. It seems i run into these issues sometimes. If what you say that maybe the cell isnt really empty. Is there a way to fix it on the actual sheet?

 

[Joe4]

Conceivably, yes. It depends on how exactly the data is being populated.

You can easily check to see if the cell is really empty with the LEN function.
Let's say the cell in question is B4. Then just enter this formula in any blank cell:
=LEN(B4)
If it returns anything other 0, then the cell is not empty.

 

[zone709]

ok good but if it returns 0. Then is there anything i can do to the format of the cell or whatever to get this cell back to nirmal state as it would pull in a formula on anormal sheet

 

[Joe4]

Then is there anything i can do to the format of the cell or whatever to get this cell back to nirmal state as it would pull in a formula on anormal sheet

I am sorry, but I do not understand what you are asking.