Does anybody here know what the method is to move to a new cell relative to another cell in a procedure? (Meaning; given a certain cell, go down a set number of rows and over a set number of columns)

For illustrational purposes, below is the procedure minus the proper (read: easiest) way of doing it. The procedure works for what I need it to do but is dependant on knowing which column I want to be in when the procedure is done. Although it works, it is a roundabout way of getting where I need to be. Although I don't normally comment my code I did so here for the sake of ease of reading it for anybody who might be able to help.

Quote:

Sub NoTaxes() Dim varTarget As Variant Dim varTemp As Variant Dim varTemp2 As Variant Dim varTemp3 As Variant Call temp408 varTemp = ActiveCell.Address 'store the current cell address in memory varTemp2 = Mid(varTemp, 5) 'retrieve the rows from the above address varTarget = "ad" & varTemp2 'assign the target address to a variable for c & p Range("ah135:ai137").Select 'copy some custom formulas saved in the workbook Selection.Copy 'load them into memory Range(varTarget).Select 'move to the target cell ActiveSheet.Paste 'paste the custom formulas Range(varTemp).Activate 'um...I'm not sure why I take this side-trip. varTemp2 = varTemp2 + 5 'add 5 rows to the place where I pasted the custom formulas varTemp3 = "aa" & varTemp2 'store the cell in column AA 5 rows down from the paste Range(varTemp3).Activate 'move to the above set cell 'Call PrintMe End Sub

If you can answer my question then you can see that the above procedure is 6-7 lines longer than it needs to be. My searches in help usually lead me either to Move which is not what I'm looking for or xlUp/xlToLeft/xlBlahBlahBlah and No help available for that. I know there is a method for moving relative to where you are but I cannot remember what it is and I'm apparently no good at coming up with a good enough search word.

N.B. Although it's not showing up in the post, the plus sign is there in line 14 of the procedure.

Thanks to anybody who can help and to anybody who tries.

**Edit**Hmph, the plus sign shows up in the post but it did not in the preview.

Is this the code from the record button? or one you wrote? If it's from one you wrote sometimes it's easier to use the record button and do the steps in the shortest moves possible and then look at the code. Also are you wanting to copy that information or move it, I'm a little confused.

I wrote it. The macro recorder will not declare variables.

I am wanting to copy the information since the procedure might be, and usually is, called multiple times.

Thanks for the suggestion and that was one of the first things I tried. Unfortunately what it records is the address of the target so if I called the procedure multiple times for multiple different targets it would return always to the one used in the recording. As I said, I need it relative to the target regardless of how many times it's called.

Would something like this help? I wrote this one for a spreadsheet at work. What it does is copy files from a certain point to another point on the sheet overwriting the formulas to adjust for the monthly readings. There are some other things involved but I'm thinking the scroll may be what you are looking for.

Quote:

TKNcopystd() ' ' TKNcopystd Macro ' Macro recorded 11/17/2005 by amy marie northam ' ' Sheets("5ppm TKN").Select ActiveWindow.LargeScroll ToRight:=-3 ActiveWindow.LargeScroll Down:=-1 Range("G5:G10").Select Selection.Copy Range("F5:F10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("C8").Select Application.CutCopyMode = False Selection.ClearContents Sheets("10ppm TKN").Select ActiveWindow.LargeScroll ToRight:=-3 ActiveWindow.LargeScroll Down:=-3 Range("G5:G10").Select Selection.Copy Range("F5:F10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("C7").Select Application.CutCopyMode = False Selection.ClearContents Sheets("1ppm TKN").Select Range("G5:G10").Select Selection.Copy Range("F5:F10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("C55:G55").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-3 ActiveWindow.SmallScroll Down:=2 Range("C15").Select ActiveSheet.Paste Application.CutCopyMode = False Range("C7").Select Application.CutCopyMode = False Selection.ClearContents End Sub

Throw a birthday cake at it and call it a day my brain hurts

um, why do you need to move to the cell to paste the info?

Range(varTarget).Select
ActiveSheet.Paste

can be replaced with:

Range(varTarget).Paste

No need to actually select the cell you want to paste to when you are using VBA.

also, if you want to paste something a certain distance from a given cell, the command you probably want to use is the offset command and/or the cells command. It's really late here and i'm not sure what the objective is. I'll check back in the morning or feel free to prill me.

*picks up + sign* Here's your sign!

You mean excel isn't just for making grocery lists on?

BTW what language are you all speaking? It isn't english!!!!!!!!!!

LOL that's an excellent use nanner

*Doing the I'm not worthy bow to choc and Rubber

I thought I was pretty profecient.

Nanner, what are u doing in the tech support section?

Quote:

Owoooo!! It still crashes with range().paste. If I use .PasteSpecial though... I'll leave it to MS to rationalize that one.

what version are you using? I've always wrote it that way. 2003 (that's what I'm on now) seems to be pickier with syntax than earlier versions. Earlier versions you could even leave off the "Range" and just use [B2].Paste instead of Range("B2").Paste. That syntax still works for me in some intances, but not in others - go figure

Quote:

Correct me if I'm wrong but you cannot in VBA define a custom method, right? Of course you can in VB but I seem to recall that you cannot in VBA.

Never say never. You can make it do whatever you need it to with a little creativity

HEY! I can fix a puter, install stuff, make a webpage, fix pictures etc etc. Just never used Excel and Word to their full extent! Did take some classes paid for by the place I use to work but of course never paid attention Then again it wasn't THIS complicated! Now I have no need for it (Not like I really did back then either)

Ummm I don't LIKE donuts really......can I have some ice cream?


Oh and I came here thinking I might learn something......but it's wayyyy over my little bitty ducky head! (and that thingy about not ever having a use for the info) Soooo......


Have 2 major puter geeks in the family....don't need to learn the complicated stuff!!!!!

ROFL! I totally feel like I entered another country with this post.