1 Replies - 2595 Views - Last Post: 12 October 2010 - 11:31 AM Rate Topic: -----

#1 greatestone4eva  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 10-May 09

VBA parse extracted number in text to double?

Posted 12 October 2010 - 10:12 AM

I have a macro that extracts a number from a cell in Excel, as text, using VBA, and I keep getting an error for type mismatch when dealing with MSRP and Price. I extract a string from a large cell containing a description formatted for web, and for this particular case, it's a dollar amount, i.e. "$1.01" in text form. I need to multiply it and paste into another column, which it'll do but it'll give me an error due to type mismatch. what should i do?

(the i in the cells reference is from a "For i = 2 to End of Sheet" loop)

'BREAK UP DESCRIPTION INTO PARTS
    Dim strSubString1 As String
    Dim strSubString2 As String
    Dim lngPos1 As Long
    Dim lngPos2 As Long
    
    ModelDescription = Worksheets("Incomplete Products Information").Cells(i, 4).Text
    
    'MSRP
    strSubString1 = "MSRP:"
    strSubString2 = "<br/><br/>"
    
    lngPos1 = InStr([1], ModelDescription, strSubString1, vbTextCompare)
    lngPos2 = InStr([1], ModelDescription, strSubString2, vbTextCompare)
    If lngPos1 > 0 And lngPos2 > 0 Then
    Worksheets("Hidden Sheet 1 - Product INFO").Cells(i, 3) = Mid(ModelDescription, lngPos1 + Len(strSubString1), lngPos2 - lngPos1 - Len(strSubString1))
    End If



    'PRICE
    Dim MSRP As Double
    MSRP = Worksheets("Hidden Sheet 1 - Product INFO").Cells(i, 3).Value 'THIS IS THE LINE Excel BRINGS ME TO WHEN I DEBUG
    Price = (MSRP * markupmult)
    Worksheets("Hidden Sheet 1 - Product INFO").Cells(i, 4) = Price






Is This A Good Question/Topic? 0
  • +

Replies To: VBA parse extracted number in text to double?

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: VBA parse extracted number in text to double?

Posted 12 October 2010 - 11:31 AM

Cut the "$" sign and then use CDbl() function
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1