Untitled
Sub PullProductDescriptionsAndDynamicColumn() Dim wsCurrent As Worksheet Dim wsPrices As Worksheet Dim lastRowCurrent As Long Dim lastRowPrices As Long Dim currentCode As String Dim dynamicHeader As String Dim dynamicHeaderDecimal As Double Dim dynamicCol As Long Dim i As Long Dim matchRow As Range Dim headerRow As Range Dim headerCell As Range ' Set worksheets Set wsCurrent = ThisWorkbook.Sheets("Paste") Set wsPrices = ThisWorkbook.Sheets("Prices") ' Find the last rows in both sheets lastRowCurrent = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row lastRowPrices = wsPrices.Cells(wsPrices.Rows.Count, "A").End(xlUp).Row ' Get the value in F1 of the Paste sheet (dynamic header) dynamicHeader = wsCurrent.Range("F1").Value ' Check if F1 contains a percentage and convert it to decimal If InStr(dynamicHeader, "%") > 0 Then dynamicHeaderDecimal = CDbl(Replace(dynamicHeader, "%", "")) / 100 Else dynamicHeaderDecimal = dynamicHeader End If ' Find the header row in Prices sheet (assume it's row 2) Set headerRow = wsPrices.Rows(2) ' Locate the column number for the header matching the value in F1 (either as text or decimal) Set headerCell = headerRow.Find(What:=dynamicHeaderDecimal, LookIn:=xlValues, LookAt:=xlWhole) If Not headerCell Is Nothing Then dynamicCol = headerCell.Column Else MsgBox "Header '" & dynamicHeader & "' not found in Prices sheet!", vbExclamation Exit Sub End If ' Loop through each code in the Paste sheet For i = 2 To lastRowCurrent currentCode = wsCurrent.Cells(i, "A").Value ' Check if the code exists in the Prices sheet Set matchRow = wsPrices.Columns("A").Find(What:=currentCode, LookIn:=xlValues, LookAt:=xlWhole) ' If a match is found, pull the description from column B and the value from the dynamic column If Not matchRow Is Nothing Then wsCurrent.Cells(i, "B").Value = wsPrices.Cells(matchRow.Row, "B").Value ' Pull description wsCurrent.Cells(i, "C").Value = wsPrices.Cells(matchRow.Row, dynamicCol).Value ' Pull dynamic column value into C Else wsCurrent.Cells(i, "B").Value = "Not Found" wsCurrent.Cells(i, "C").Value = "Not Found" End If Next i MsgBox "Descriptions and dynamic column values updated!", vbInformation End Sub
Leave a Comment