CODEWITHSUNDEEP
vbaexcel
Stliiyone
Stliiyone

Reputation: 13

Excel VBA Find value in column, and do math

I have

Column A - with Names
Column B - with Quantities
Column C - Where I want returned Value of Quantity x Cost
Column E - with Names but located in different cells
Column F - with Prices

What I'm trying to achieve is: Take value from A1 and find it in E:E (Lets say we found it in E10), when found take value of B1 and multiply it by Respective value of F10 - and put all this in Column C

And so on for all values in column A

I was trying to do it with Do while and two variables x and y, but for some reason it doesn't find all values only for some rows.

Thank you in Advance.

Sub update_button()
'calculates money value for amazon sku
Dim x, y, z As Integer 'x, y, and z variables function as loop counters

'Loop through added SKU/Prices
For x = 4 To 25000
If Worksheets("Sheet1").Range("H" & x) = "" Then
'Blank row found, exit the loop
Exit For
End If


  'Loop through Column E to find the first blank row to add the value from H into
For y = 4 To 25000
    If Worksheets("Sheet1").Range("E" & y) = "" Then
    'Blank row found, Add SKU and Price
    Worksheets("Sheet1").Range("E" & y) = Worksheets("Sheet1").Range("H" & x)
    Worksheets("Sheet1").Range("F" & y) = Worksheets("Sheet1").Range("I" & x)

    'Blank out Columns H and I to prevent need to do it manually
    Worksheets("Sheet1").Range("H" & x) = ""
    Worksheets("Sheet1").Range("I" & x) = ""
    Exit For
    End If
Next y
Next x

'---NOW THIS IS WHERE I HAVE THE PROBLEM



'Get Values
Dim intCumulativePrice As Integer
'Loop through report tab and get SKU
x = 4 'initialize x to the first row of data on the Sheet1 tab
Do While Worksheets("Sheet1").Range("A" & x) <> "" 'Loop through valid SKU's to find price of item

y = 4 'initialize y to the first row of SKUs on the Sheet1 tab
Do While Worksheets("Sheet1").Range("E" & y) <> ""
If Worksheets("Sheet1").Range("E" & x) = Worksheets("Sheet1").Range("A" & y) Then 'Check if current SKU on Sheet1 tab matches the current SKU from SKU list



    'Calculates the total
    intCumulativePrice = intCumulativePrice + (Worksheets("Sheet1").Range("B" & y) * Worksheets("Sheet1").Range("F" & x))
     ' Puts Quantity X Price in Column B agains every Cell
     Worksheets("Sheet1").Range("C" & y) = (Worksheets("Sheet1").Range("B" & y) * Worksheets("Sheet1").Range("F" & x))
Exit Do
End If

y = y + 1
Loop
x = x + 1
Loop
'Puts Grand total in Column L Cell 4
Worksheets("Sheet1").Range("L4") = intCumulativePrice

'Show messagebox to show that report processing has completed
MsgBox "Report processing has been completed successfully", vbInformation, "Processing Complete!"
End Sub

Upvotes: 0

Views: 5677

Answers (3)

Reafidy
Reafidy

Reputation: 8431

Try this:

Sub HTH()

    With Worksheets("Sheet1")
        With .Range("A4", .Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
            .Formula = "=VLOOKUP(A4,E:F,2,FALSE)*$B$1"
            .Value = .Value
        End With
    End With

End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53135

To do this with VBA you should copy the source data to Variant arrays and loop over those. Much faster and IMO easier to read and debug.

Something like this

Sub Demo()
    Dim Dat As Variant
    Dim PriceList As Range
    Dim PriceListNames As Variant
    Dim PriceListPrices As Variant
    Dim Res As Variant
    Dim sh As Worksheet
    Dim i As Long
    Dim nm As String
    Dim nmIdx As Variant
    Dim FirstDataRow As Long

    FirstDataRow = 4
    Set sh = ActiveSheet
    With sh
        Dat = Range(.Cells(FirstDataRow, "B"), .Cells(.Rows.Count, "A").End(xlUp))

        Set PriceList = Range(.Cells(FirstDataRow, "E"), .Cells(.Rows.Count, "F").End(xlUp))
        PriceListNames = Application.Transpose(PriceList.Columns(1)) ' Need a 1D array for Match
        PriceListPrices = PriceList.Columns(2)

        ReDim Res(1 To UBound(Dat, 1), 1 To 1)
        For i = 1 To UBound(Dat, 1)
            nm = Dat(i, 1)
            nmIdx = Application.Match(nm, PriceListNames, 0)
            If Not IsError(nmIdx) Then
                Res(i, 1) = Dat(i, 2) * PriceListPrices(nmIdx, 1)
            End If
        Next

        Range(.Cells(FirstDataRow, 3), .Cells(UBound(Dat, 1) + FirstDataRow - 1, 3)) = Res
    End With
End Sub

Upvotes: 1

Stewbob
Stewbob

Reputation: 16899

You can do this with a simple VLOOKUP formula in column C.

=VLOOKUP(A1,E1:F65000,2,FALSE)*B1

You can also use a named range for the data in columns E and F, so you don't have to rely on a fixed address like E1:F65000.

Upvotes: 2

Related Questions