Formula within replace statement?

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
I may be looking to deep at his but essentially I am getting a value (Price) from a websites source code, cleaning that info up, and then need to convert the value into USD.

If need be I could simply create a new column for for aesthetics I would like all conversions to be on one page. I have the conversion rate on sheet6 B2

This code does not have any attempts at converting the price just simply inputs it.
VBA Code:
Sub LPwilliams()
    Dim IE As Object
    Dim Price As String
    Dim i As Long

    Set IE = CreateObject("internetexplorer.application")
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        With IE
            .Navigate ("https://www.triumph-spares.co.uk/search?q=" & Cells(i, "A").Value)
            .Visible = False
        End With
    
        Do While IE.readystate <> 4: Wait 5: Loop
    
        DoEvents
        
        On Error Resume Next
        Price = IE.document.getElementsByClassName("price price-new")(0).innertext
        Cells(i, "C").Value = Price
        
        If Err.Number = 91 Then
            Cells(i, "C").Value = "N/A"
        End If
        
        Cells(i, "C").Replace What:="EX VAT", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Cells(i, "C").Replace What:="£", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Next
        
End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

crazyeyeschase

Board Regular
Joined
May 6, 2014
Messages
88
Essentially I thought putting this at the end would work but it doesn't.

VBA Code:
Cells(i, "D").Value = Cells(i, "C").Value*Worksheets("sheet6").Range("B")
 

Watch MrExcel Video

Forum statistics

Threads
1,126,972
Messages
5,621,901
Members
415,864
Latest member
cybid

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