range referencing

[Peter]

here is my sub, (thanks to Ryan H, et al, in post 23/9/08):
it finds the last full cell on sheet 1 and copies down to it and pastes the
result in the next avaialble col (rhs) on sheet 2.
**************************
Sub CopyCols()

Dim LRow As Long
Dim LCol As Long

' find last row in Col.A on Sheet1
LRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

' find last column in Row 1 on Sheet2

LCol = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column

' copy range on Sheet1 and paste the range onto Sheet2

Sheets("Sheet1").Range("c1:e" & LRow).Copy _
Destination:=Sheets("Sheet2").Cells(1, LCol + 1)


End Sub
****************
this works perfectly, thanks guys, but i would like to automate it a bit
more and i just cannot figure out range cell referencing vis as vis cell
referencing.

can someone pls show me how to make a variable out of
Range("c1:e" & LRow). (no message boxes or userforms pls)

such that i can at the top of my code (or anywhere really) slip in the col
number or column numbers i want to copy and paste instead of changing the
range in the current specification. eg instead of going to the line "c1:e"
and putting in a range reference, i would like to just put into the code
somewhere that i want col 3 and col 5 and col 7 copied and be able to do it
again and again but with different col numbers.

ps i have in the past, learnt sas and spss but they were a piece of cake
compared to this. i am just finding it so so hard to learn. i have a couple
of books but am still having a lot of trouble getting my head around the
objects and methods etc. so sorry for asking really dumb questions. i guess
it will click all of a sudden. any tips to pick up referencing to cells
ranges etc would be good.

 

[JLGWhiz]

Open your VB editor, Alt + F11, click on Help and type this in the help
search window:

How to Reference Cells and Ranges

Then click on it in the display panel and it will bring up the help file
that explains cell referencing. Once you get that down pat, you can then use
variables to manage the cell references.

 

[Bob Bridges]

Hi, Peter. No InputBoxes either, eh? Ok, I'm taking you literally instead
of asking questions -- there may be more convenient ways to do this but lets
go for a little instant gratification heer if possible -- try this:
Somewhere near the top of your code where you don't have to look for it,
where you can just go in and change it as you said you wanted, put this line:

Const CopyCol = 3

Remember, you said you wanted to be able to enter a column NUMBER rather
than a column letter, so this will do it for you. In the rest of your
program, then, CopyCol will refer to "3" and you can use that wherever you
can use a 3 to refer to a column.

But of course the format you're used to for ranges uses a letter, right? eg
Range("A3:C5"). To use a column number instead, use a form of the Range that
specifies two Cell objects, the top left and the bottom right Cells of the
Range, where each Cell object specifies a row and column number. For example
Cells(5, 3) refers to row 5, column 3, ie C5. Doing Range("A3:C5") using
Cell references looks like this:

Range(Cells(3, 1), Cells(5, 3))

That's a range from row 3 col 1 at the top left, to row 5 col 3 at the
bottom right, ie A3 to C5. So you could use CopyCol in place of one of those
column numbers:

Sheets("Sheet1").Range(Cells(1, CopyCol), Cells(LRow, CopyCol+2)).Copy _
Destination:=Sheets("Sheet2").Cells(1, LCol + 1)

You didn't say exactly how you wanted to use the CopyCol value, but I hope
you can see how it works here.

 

[Bob Bridges]

I'm an experienced programmer, too -- worked with PL/1, ALGOL, BASIC, REXX,
just about a score of languages for a couple decades, yet objects and methods
gave me lots of difficulty for a year or two. In the end it wasn't my own
efforts that resulted in my "click", but a VB programmer who gave me a couple
tips; after a little discussion of collections and writing my own classes
suddenly a lot became clear to me that I'd simply been confusing with unlike
concepts. (Methods, for example; it was clear to me that a method was a
function or subroutine, and I couldn't figure out why I kept getting that
stupid "not supported by this object" message.) I mention this because I'd
be happy to pass the favor on: If you want to spend a little time talking
about this and getting your head really and truly wrapped around it, drop me
an email (my address is in my profile) and we can work through it until you
get it...or get it at least to the extent that I do .

 

[Peter]

hey bob, youre magic mate. thankyou ever so much for going to so much trouble
to help me.
re finding your email address, i dont know how to access the profile as you
suggested. could you pls just email me at <[email protected]>
and i can make contact with you.
regards,
peter