Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

IF/ VLOOKUP in a macro



I need to perform a check-copy/paste based on cell value and ai want it automated with macro. And because I can't manage to do it for 2 days, I feel I need help :)

So, based on the order and item, I want to fill an empty cell , only if it's empty, with data from sub-items, but only if the sub-item is from the same order. I attach example of the data and the desired outcome.

Thanks in advance :)



Hello DiKa and welcome to the forum.

After looking at your sample file, I'm at a bit of a loss to fully understand how cells J, K, and N in the results table get filled in. You state "if the sub-item is from the same order", but J6, K6, and N6 are empty even though row 6 is part of order 44. Likewise, J9, K9, and N9 are empty but are part of order 50; and rows 14, 15, and 16 are part of order 50 and are also empty. Also, order #50 shows some items shipping by "Sea" and some by "Road".
It's possible to use in-cell formulas or VBA to populate the empty cells (copy/paste) but more info is needed. What criteria will be checked/compared to determine what to put in the empty cells?
If you can provide more details one of us should be able to provide you with a solution.
WillieD24 (rep: 372) Nov 15, '23 at 12:37 pm
Hi WillieD24,

J2, K2 & N2 are filled because both from order "73" and item 100, as item 101 is the sub-item of 100. J6, K6 & N6 are empty becasue - yes, both order 27, but item 100 and there is no 101 data to copy to item 100, while J7, K7 & N7 are filled becasue it's order 27, but item is 200-201. So line 7  took data frrom 8, becasue the line is item 201 - a sub-item of 200 and line 6 is empty becasue it's item 100 and it's info should come from item 101.
To recap - Hdr item (100/200/300/etc) can take info from it's sub-item (101/201/301/etc), but item 100 shouldn't take froom item 200 evenif both from the same order. So the validation should include the Order/item, but only copy if isub-item data for this Order-item is there. Not sure I expain it ok..
DiKa (rep: 2) Nov 15, '23 at 12:58 pm

Like Willie, I was puzzled by your question. Having looked at your discussion point above however, I think you mean:

If in column M, the Item number ends in a 1, say 701, then the "parent" item, say 700, gets the 701 values for columns J, K and N (even if one or more is blank).

If that's right, please say so. If not. please correct.

@Willie- I'll leave you to do this one. If you don't have time to reply, please say and I will provide an answer, probably tomorrow.
John_Ru (rep: 5722) Nov 15, '23 at 1:36 pm
Hi John_Ru,

Yes, you got it, but they can't be blank - if there is data in J, there will be data in K &N, but it's irrelevant - capy whateve is in the "child"; but have in mind - that is only if the value in L "grandparent" matches.
Ex.: If A2 is blank, and A3 isnot blank, and C2 = C3, and D2 ends with '00' when D3 starts with same as D2 (I only imagine the logic - it can be D3-1=D2), Then copy A3 to A2; B3 to B2, E3 to E2; Next line, etc....   So the result is as J-N columns.
DiKa (rep: 2) Nov 15, '23 at 3:18 pm
Thanks DiKa. 

I assume your real data is bigger than the example data so the only other questions are:

1) how and when is the source data created and updated? 

2) are you expecting to extract the desired data from the source via a button click? 
John_Ru (rep: 5722) Nov 15, '23 at 5:06 pm
Thank you both!

I gave left and right table - left is the AS-IS, right - the wanted outcome.
Please, don't focus on J,K & N, rather on A,B & E.
Yes, it's a lot more than 5 lines and 6 columns and yes, I want to achieve the following: I open my file, I click View, Macro and run it; it populates the cells as per the steps requested.

I already have few other steps in the VBA, but struggle to put together the IFs for this task. Can you halp me with this, reagrdless of what my source or amount of data is, please?

If my explanation is still not clear - tell me, but let's not focus on what is my source data, rather is it possible to achieve the simple result with the given example.
Thanks :)

DiKa (rep: 2) Nov 15, '23 at 5:19 pm

Having more columns makes it challenging (defining which column is the item number). It would help if you added a second representative Excel file (with no personal data) to your question - edit and use the Add Files button. 

Also, if you already tried to write a macro, please include that (possibly in that second file) 
John_Ru (rep: 5722) Nov 15, '23 at 6:10 pm

I'm still a bit confused. Why are (as you say) the "grandparent" cells dependant on the "child" record cells? It is more common for the "grandparent" cells to have data and the "child" cells are dependant on the "grandparent" cells.
If you could provide a file with your existing VBA code, that would probably shed some light on this. Debugging existing code usually results in better and faster answers.

I don't know how much time I can devote to solving this, so if you have time to take a stab at it - go for it.
WillieD24 (rep: 372) Nov 15, '23 at 8:22 pm
@Willie- understand, I've posted an Answer.
John_Ru (rep: 5722) Nov 16, '23 at 10:34 am
Add to Discussion


Selected Answer

DiKa (Diana)

In the attached, revised file, your example worksheet is now at the end, renamed "Side by side example". (It can be deleted once you've compared results from the macro below).

My solution is to have a worksheet "Source" where you should paste your new raw data (however many columns and rows)..The macro clears then updates the extra (second) sheet called "Output".

Before running the macro, please take a look at the sheet "Output".- I've left some false data in (for the macro to erase).

In the code below, .  The looping depends on the VBA form Cells(<row number >, <column number>) so e.g. Cells(2,3) is cell C2.

The macro (in Module 1) is as follows. I've added comments for your understanding:

Sub UpdateParents()

    Dim WsS As Worksheet, WsO As Worksheet
    Dim Rw As Long, LastRw As Long, LastCol As Long
    Dim ChildCol As Long, GrndParCol As Long
    Dim ParCell As String

    ' name source and output sheets
    Set WsS = Worksheets("Source")
    Set WsO = Worksheets("Output")
    ' state which column contain chld and parent items (assumed to be complete for all rows), same for grandparent
    ChildCol = 4
    GrndPar = 3

    ' find last used row and column (from header row 1)
    LastRw = WsS.Cells(Rows.Count, ChildCol).End(xlUp).Row
    LastCol = WsS.Cells(1, Columns.Count).End(xlToLeft).Column

    'Clear output sheet
    ' add values from first and second rows to output (missed in the loop below)
    WsO.Range(WsO.Cells(1, 1), WsO.Cells(2, LastCol)).Value = WsS.Range(WsS.Cells(1, 1), WsS.Cells(2, LastCol)).Value

    ' loop down used rows from row 3
    For Rw = 3 To LastRw
        ' check for same grandparent and if last digit isn't 1
        If WsS.Cells(Rw - 1, GrndPar).Value = WsS.Cells(Rw, GrndPar).Value And Right(WsS.Cells(Rw, ChildCol), 1) <> "0" Then
            ' if not 0, save the parent value
            ParCell = WsS.Cells(Rw - 1, ChildCol).Value
            ' overwite the previous row
            WsO.Range(WsO.Cells(Rw - 1, 1), WsO.Cells(Rw - 1, LastCol)).Value = WsS.Range(WsS.Cells(Rw, 1), WsS.Cells(Rw, LastCol)).Value
            ' overwrite that with saved parent value
            WsO.Cells(Rw - 1, ChildCol) = ParCell
        End If

        ' write this row to output anyway
        WsO.Range(WsO.Cells(Rw, 1), WsO.Cells(Rw, LastCol)).Value = WsS.Range(WsS.Cells(Rw, 1), WsS.Cells(Rw, LastCol)).Value
    Next Rw

    ' state (in red) when Output was created
    With WsO.Cells(1, LastCol + 2)
        .Value = "Output created " & Now
        .Font.Color = vbRed
    End With
    ' switch to output sheet

End Sub

Run the code and it will erase then add values to the output sheet. Finally it will move to that sheet and  (in row 1, two columns to the right of the data) it will confirm when the output was created, in red.

Hope this fixes your problem. If so, please remember to mark this Answer as Selected.


That is great!! Thank you do much!



Glad that worked for you, Diana. Thanks for selecting my Answer.

Hope you can see how to change the reference columns for your real data set. I wasn't sure what happens with sub items like 402 but you can come back with a new question if there's a problem.
John_Ru (rep: 5722) Nov 16, '23 at 11:04 am
For future, please note that an Answer should be a solution. Your comment above (and my reply) should really be under my Answer therefore.
John_Ru (rep: 5722) Nov 16, '23 at 11:13 am
Nice one. Deleting and rebuilding the "Output" was something I hadn't considered. I'm still at a loss to understand why the data for A,B & E isn't entered when the other data (C, D) is entered. Diana must have some reason. Too bad the file posted didn't have her code included for us to inspect and debug.
WillieD24 (rep: 372) Nov 16, '23 at 11:38 am
@Willie- thanks. I was puzzled about the data entry too too but went with it. Agreed on debugging user code- sometimes it's just macro-recorder stuff, sometimes ill-conceived but in most cases it helps to see their ideas and add a little knowledge to create a solution which is partially theirs anyway.
John_Ru (rep: 5722) Nov 16, '23 at 11:54 am
BTW we're both discussing under Diana's "Answer", not mine! :)
John_Ru (rep: 5722) Nov 16, '23 at 11:55 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login