Trying to use a module to fill prices

[Darren Smith]

Using VBA to find prices then fill prices to a different worksheet.
I created a code below but it won't run it skips over the code and nothing happens.

Sub UpDate_Stock_Prices()

        Dim JCMLastRow As Long, x As Long
        Dim DataRng As Range
        Dim PartsList As Worksheet
        Dim PartsListLastRow As Long
        Dim JCM As Worksheet

        
        Set Lines_Color = Body_And_Vehicle_Type_Form.Add_Lines_And_Color
        
        Set JCM = ThisWorkbook.Worksheets("Job Card Master")
        Set PartsList = ThisWorkbook.Worksheets("Parts List")
        
        JCMLastRow = JCM.Range("D" & Rows.Count).End(xlUp).Row
        PartsListLastRow = PartsList.Range("A" & Rows.Count).End(xlUp).Row
        
        Set DataRng = PartsList.Range("A2:B" & PartsListLastRow)
        
        For x = 13 To JCMLastRow
        
        On Error Resume Next
        
           JCM.Range("O" & x).Value = Application.WorksheetFunction.VLookup( _
                JCM.Range("D" & x).Value, DataRng, 2, False)
                 
        
        Next x
        
        ThisWorkbook.Worksheets(PartsList).Columns(2).Calculate
             
           End If

    End Sub

 

[Joe4]

I think it would be helpful to see what your data looks like to help analyze it. Can you post a small sampling?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Darren Smith]

I think I will send my workbook over to you via dropbox
The code is no longer a Module
I have changed my code see below and it now runs but won`t fill prices in column O in sheet "Job Card Master" from Sheet "Parts List" column B

Private Sub Add_Lines_And_Color_Change()

    TurnOff

       Call RefreshSpecificPowerQuery("Parts List")
      
    Dim PartsList As Worksheet, JCM As Worksheet
    Dim PartsListLastRow As Long, x As Long, JCMLastRow As Long
    Dim DataRng As Range
   
    Set JCM = ThisWorkbook.Worksheets("Job Card Master")
    Set PartsList = ThisWorkbook.Worksheets("Parts List")
       
        JCMLastRow = JCM.Range("A" & Rows.Count).End(xlUp).Row
        PartsListLastRow = PartsList.Range("A" & Rows.Count).End(xlUp).Row
       
        Set DataRng = PartsList.Range("A2:B" & PartsListLastRow)
       
        For x = 13 To JCMLastRow
       
                JCM.Range("O" & x).Value = Application.WorksheetFunction.VLookup( _
                JCM.Range("D" & x).Value, DataRng, 2, False)
                
       
        Next x
       
        ThisWorkbook.Worksheets(PartsList).Columns(2).Calculate
       
        TurnOn

End Sub

 

[Darren Smith]

Workbook is here

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[Joe4]

I cannot download files from my current computer (work computer which forbids the download of files, especially files with code, from the internet).
Perhaps someone else will be able to take a look at it. If not, I can take a look at it later tonight when I am at my home PC.

 

[Darren Smith]

Please can you look at it later?
Every time I try to use L2BB my excel crashes?

 

[Darren Smith]

I`ve found a solution to the issue sorry to bother you

 

[Joe4]

I`ve found a solution to the issue sorry to bother you

No worries. Thanks for letting me know.
Would you mind posting the solution back to this thread?

 

[Darren Smith]

Morning here is the correct code

Private Sub Add_Lines_And_Color_Change()

Call RefreshSpecificPowerQuery("Parts List")

    Dim PartsList As Worksheet, JCM As Worksheet
    Dim PartsListLastRow As Long, JCMLastRow As Long, x As Long
    Dim DataRng As Range
    
    Set JCM = ThisWorkbook.Worksheets("Job Card Master")
    Set PartsList = ThisWorkbook.Worksheets("Parts List")
        
        JCMLastRow = JCM.Range("A" & Rows.Count).End(xlUp).Row
        PartsListLastRow = PartsList.Range("A" & Rows.Count).End(xlUp).Row
        
        Set DataRng = PartsList.Range("A2:B" & PartsListLastRow)
        
        For x = 13 To JCMLastRow
                 On Error Resume Next
                JCM.Range("O" & x).Value = Application.WorksheetFunction.VLookup( _
                    JCM.Range("D" & x).Value, DataRng, 2, False)
                    
                 If JCM.Range("P" & x).Value = 0 Then
                 JCM.Range("P" & x).Value = ""
                 End If
                 
        
        Next x
                   
        End Sub