Excel/VBA/SQL Grab value from one row and update in another

Question

What I'm trying to do is update amount from one line to another in Excel. They don't have any key that combines but they are in pattern. Always start with Item Group and about 3-7 lines after is End of Item Group. I want to grab the amount from End of Item Group and update the line in Item Group but always Item Group will be first then End of Item Group.

I could upload to SQL if it's easier to work on or Access.

This is the problem.

This is what I'm trying to accomplish: After this update I wanted to delete every line after item group but only up to end of item group.

Would you be okay with a macro? – Kubie Nov 20 '18 at 15:14 — Kubie, Nov 20 '18 at 15:14
Yeah, It's even better so I could learn & train. – Magickarp Nov 20 '18 at 15:17 — Magickarp, Nov 20 '18 at 15:17

score 0 · Answer 1 · answered Nov 20 '18 at 15:28

So basically if you don't already have the Developer tab, you would need to go to Options > Customize Ribbon > Click 'Developer'

Then click on VB Editor on Developer tab and right click in project area and select "insert new module". In the new module insert this code and save.

To make it easy to run, you can go to Developer tab > Insert > Button. Then right-click on the button and assign it to this macro you just created so everytime you want to update the item group values you can click the button. If the button doesn't let you "assign a macro" then you probably inserted the wrong type of button.

Option Explicit

Sub UpdateValues()

    Dim itemCol As Long
    Dim lastRow As Long
    Dim row As Long
    Dim lastEnd As Double: lastEnd = 0.0

    ' set reference to your worksheet
    With ThisWorkbook.Worksheets("your sheet name")

        ' set column with title 'Item_Type' in Row 1
        For row = 1 To 20
            If Trim(CStr(.Cells(1, i).Value)) = "Item_Type" Then itemCol = i
        Next

        ' find last row of Item_Type column
        lastRow = .Cells(.Rows.Count, itemCol).End(xlUp).Row

        ' loop backwards thru rows
        For row = lastRow To 1 Step -1

            ' if cell contains word 'end'
            If InStr(LCase(CStr(.Cells(row, itemCol).Value)), "end") Then
                ' set our group value
                lastEnd = .Cells(row, itemCol+1).Value
            ' if lowercase(cell) = 'item group'
            ElseIf LCase(CStr(.Cells(row, itemCol).Value)) = "item group" Then
                ' put end value in the cell
                .Cells(row, itemCol+1).Value = lastEnd
            End If

        Next row

    End With

End Sub

Excel/VBA/SQL Grab value from one row and update in another

1 Answers1