Using variables in a Range 3

[rybred]

Im quite new to VB, basically i've seen a range specified like:

Range("B4,C4,F4,H4&quot.Select

I was just wondering how i could get variables to specify a range of cells. I need the macro to paste data, in columns on the same row, the column will not be changing, but the macro will be run in different rows.

Thanks for any input

 

[VulcanJedi]

To access data from VBA and XL sheets, you can you various techiniques, namely, cells() or range().

Range uses the (r1,c1) nomenclature, while cells, uses an index like relating to the cartesian coordiante system
ie:
Range("A1&quot.select or Cells(1,1).select
Thus to use variables to identify data points you'll have to realize that you may need to convert numerical values into the strings that range() requires. However range does offer the additional funtionality of reference a larger group of cells. Use the .address property or .usedrange property(see VBA help F1 for explicit example of application), I think this should easily help you get on your way Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[rybred]

It worked, it does what i originally wanted, pretty basic i know. I used Cells(), im sure the code is quite non-efficient, but i really dont know any vb. Thanks much for the info tranpkp! Here is the macro:

Sub Proprod()

Dim idrowp
Dim idrowc
Dim frmentr

idrowc = InputBox("Enter Current Row!", "Current Row&quot
idrowp = InputBox("Please enter the product row number!", "Row ID&quot
frmentr = "=[pricedata.xls]MAIN!R"

ActiveCell.FormulaR1C1 = (frmentr & idrowp)
Selection.Copy
Cells(idrowc, 3).Select
ActiveSheet.Paste
Cells(idrowc, 7).Select
ActiveSheet.Paste
Cells(idrowc, 8).Select
ActiveSheet.Paste
Cells(idrowc, 12).Select
ActiveSheet.Paste
Cells(idrowc, 16).Select
ActiveSheet.Paste
Cells(idrowc + 1, 2).Select
End Sub


yeah it isnt much of a coder's code
any advice would be great, such as how the code can know what row the active cell is on.

 

[VulcanJedi]

use currentRow = activecell.row
also you shouldn't need to use copy/paste.
if you are just duplicating data, just do

cells(activecell.row, newC)=activecell
(this will input the value of the active cell into the cell of the current row, yet new column), if wanted done repeatedly put it in a for loop, you can prompt the user w/ inputboxes if you want, dependant on the functionatliy you are using for otherwise try to program so the user doesn't have to input anything...
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[VulcanJedi]

Anywyas meant to say before last comments...........
GO HAWKEYES!! Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[lespaul]

Another suggestion:

if the columns are always the same (3,7,8,12,16,2) put these as constants into an array and then (forgive any incorrect syntax, I mostly use Delphi which is slightly different)

for i = Low(Array) to High(Array) do
Cells(idrowc, Array) = activecell

Good Luck! Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[rybred]

I'm not exactly sure on how to get the loop to work, im just guessing:

Dim columnGroup As Variant
columnGroup = Array(2, 3, 7, 8, 12, 16)
Dim i As Integer
....

For i = ? To ?
Cells(currentRow, columnGroup).Activate
ActiveSheet.FormulaR1C1 = (dataLink & productRow)
Next i

i renamed the variables also.

 

[lespaul]

Try this (I haven't tested it or anything, but it's the basic format):

Dim ColumnArray (6) As Integer
Dim i as integer

ColumnArray(0) = 2
ColumnArray(1) = 3
ColumnArray(2) = 7
ColumnArray(3) = 8
ColumnArray(4) = 12
ColumnArray(5) = 13

For i = LBound(ColumnArray) to UBound(ColumnArray)
Cells(currentRow, columnArray(i)).FormulaR1C1 =
(dataLink & productRow)
Next i

So the lower bound of the array is 0 and the upper is 6. So each time i changes the value of ColumnArray(i) changes to what's in that slot of the array (2, 3, etc.).

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[rybred]

that array code works great, and with the activecell.row as a variable there is only one input the user has to enter.

Thank you to all for the tips and info

 

[VulcanJedi]

Man I can't believe after 60+hrs last wk b/w 2 jobs I am still on this site on the weekend?! Ugh. Hope it isn't for nothing. Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates navigating through the threads / posts!

 

[lespaul]

rybred,

Glad to be of help!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[VulcanJedi]

I'll give you a star Leslie! Hey I just saw I was the 'TipMaster of the week'?! Believe that Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[lespaul]

Wow! My first star, I feel that I may have a clue!!

Congrats on the TipMaster status!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[DaleWatson123321]

Congratulations, Leslie.

===> Another one coming your way ...you've earned it.

Regards, ...Dale

 

[lespaul]

Thanks Dale!! Coming from you that means ALOT (since you're the VBA guru!). Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned