Offset from the precedent in formula in another Sheet

[swallis]

Numerical sheets refer to "base" sheet. I would like to have a formula or other vba which allows me to find a value in Base and return it in the numerical sheet.

F2 in "1" = F2 in "base" - I need to insert K2 from "Base" into J2 in "1"
F2 in "2" = F26 in "base" - I need to insert K26 from "Base" into J2 in "2"
F2 in "3" = F51 in "base" - I need to insert K51 from "Base" into J2 in "3"

And so on - the 25 row gap is constant.

Any help would be appreciated

steve

Please Login or Register  to view this content.

[swallis]

I'm still struggling with this and have come up with the following as an alternative to my original post. Running only the first WITH statement works perfectly, but when I continue the procedure there are 2 problems. First, the second WITH (even if I manually select Sheet 2 first) runs its formula in sheet 1. Secondly it gives a #VALUE! result.

As will be shown by my fumbling attempts, my expertise is not great. Any assistance with either lot of code would be very gratefully received.

Steve

Please Login or Register  to view this content.

[swallis]

Have played some more and by adding "Set RngColF = Range("f2", Range("f" & Rows.Count).End(xlUp))" after each sheet activation, it removes problem 1. Not pretty, but one step in the right direction.

Steve

[FDibbins]

It would probably help if we could see what are working with? Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[swallis]

Thanks for your reply Ford. I've attached a file and altered my second lot of code as follows. Both efforts should produce the same result in the sample file Sheet 1, but I hadn't got to the IF statement with the first attempt, so it only looks at one column (if it worked), so sheet 2 would give a different result for the first lot of code.

Appreciate any help you can provide. I need to go out for a few hours, so please don't think me rude if I don't respond to any further advice immediately.

Steve

Please Login or Register  to view this content.

[swallis]

Perseverance Pays! Finally have worked out some code that works. It looks cumbersome to me (there will be 10+ sheets) and if any body can show me a better way I'd be grateful.

Will leave open for a day or so in hope, then mark Solved.

Steve

Please Login or Register  to view this content.