Trying to use a module to fill prices

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
415
Office Version
  1. 2019
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
415
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2020
Messages
415
Office Version
  1. 2019
Platform
  1. Windows
Please can you look at it later?
Every time I try to use L2BB my excel crashes?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
415
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
415
Office Version
  1. 2019
Platform
  1. Windows
Morning here is the correct code

VBA 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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,365
Members
416,856
Latest member
silentir

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