Macro to update column data with vlookup result

Question

I have two spread sheets, Sheet1 & Sheet2.

Sheet2 will be a paste of a lot of information received from a third party with a unique ID in column B, I’m currently using vlookup but the spread sheet is getting very large and taking a long time to load up and save.

In sheet 1 I'd like to use a macro to insert the vlookup result from a MACRO matching the unique ID in column B and displaying the value of column c from Sheet2.

Hope this makes sense, thank you!!

Tried a lot:

Sub UPDATE()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    v1 = s1.Range("A2")
    v2 = s1.Range("B2")
    s2.Activate
    For Each r In Intersect(ActiveSheet.UsedRange, Range("B:B"))
        If r.Value = v1 Then
            r.Offset(0, 1).Value = v2
        End If
    Next
End Sub

Answer 1

If you can post a non-question in the question field, then can I post a non-answer in the answer field?

The help you need right now is not the help you kinda, sorta, but not really asked for. You just don't know it yet.

If I stop judging your intent between the lines and ignore your insulting response to a genuinely helpful comment. If I take the time and energy to focus on your problem and semantically transform your demand into a question. Then you still will not get what you asked for.

I shall demonstrate.

A large workbook with 2 sheets loads and saves slowly. Presumably because of its size and numerous vloookup functions. To improve performance you want a macro on sheet 1 to “insert the vlookup result from a MACRO” on sheet 2.

This may surprise you. If a formula is already optimized then replacing it with another formula will not improve performance. Even if that formula is a vlookup and even if that vlookup is placed by a MACRO. In fact, it will be slower because you're still calculating the vloookups and now you've added the overhead of running the macro on sheet 1 and the MACRO on sheet 2. Seems to me like you might get a performance boost by changing your calculation settings to calculate before save and do not refresh or calculate when opening. That won't improve your save time but opening and working with document should be faster.

That's an answer you didn't ask for but it's weak and really doesn't do much to get to the root. I'm really trying to help so I won't stop yet I'll dig through the code you shared and focus on the pieces where you think the problems are. Oh, you didn't say where they could be. No big deal, it's a short block and I'm almost VBA literate, I'm sure I won't have any issues fixing bugged code as I step through it in my head without access to test data or your runtime environment. Piece of cake!

Umm… what are you trying to do? I think I understand what you said you want; but I don't see how you actually went about doing it. I'll work with what you gave me but it isn't much so I hope you don't have high expectations.

Sheet 1 and 2 are set. 2 is activated and this sub starts working with it; therefore, this must be the sheet 2 MACRO. How does this interact with the sheet 1 macro? Are you certain there's no problem with that one?

Hrmmmm, I've read a little about the speed of intersection. If i recall correctly, it's supposed to be quite fast, that might be a good choice. I'll have to remember that. Uh, B:B, the whole column? You can often gain performance by narrowing constraints, but I am not certain in this case. Maybe intersection is a of short circuiting every row that follows, i should look into that.

I have to assume v1 and v2 are variants because they aren't declared and no information is given regarding option explicit or publicly scoped variables. You have to be careful variants because they can change type and cause you to throw errors all day. Especially if your code isn't clear enough to identify them on sight. Case and point, you assign 2 variants equal to a range, is this intentional or did you forget the word set? Further down you assign the value of a range to the same variant leaving me to guess about your intent.

Does your data use strings? If it does then value2 should be faster than value.

I'm confused about your loop I don't know why you have it or why you're do what you do. You said you have vlookup results pasted in B - so it makes sense that you read it. But why in a loop? You calculated a valid range, why not write if to an array in one step instead of potentially hundreds or thousands? Why are you writing to column C in sheet 2? I would have expected sheet1.

Wait, that's it? That's all your code does? They're must be something else because there is no way for me to get from where you are to where you said you want to be without acting like your personal code writing service.

So there it is. In detail. A genuine attempt to give you what you asked for. How much closer to your goal are you now that I've spent more than 2 hours offering my hand?

Probably not much.

Unsurprisingly the most helpful piece of information, the one that will get you the closest to your goal, was given to you in the first comment and it reflects poorly on you to respond in the manner you did.

From my perspective, it looks like you haven't figured out what you need to know. I'd start with with option explicit. Then transferring data between sheets. Then using ranges with arrays. That should get you to a point where we can actually help you. That's 3 topics and 1 of them is a 20 second read.

Good luck, and try to lose the attitude.