VBA to paste with dynamic range [SOLVED]

[blcexcel]

I'm a novice VBA user. This seems like it would be easy but I can't figure out how to accomplish it. I'm trying to create a template that can be used to upload data into a system. I have half of it working. Now I need to take column of consecutive values and paste them next to another column of values. The trick is the range will be dynamic. I have values for the range calculating if that helps. For example the first value will need to start at row 6 and end on row 1,000. The second value will need to start at 1,001 for and end at 2,000 and so on. I would attach a file but I'm new to this forum and I don't see how to attach a file. Thanks in advance for any assistance.


Input Table:

[pre]

Merch List	Start	End
100_5000	6	1000
100_5001	1001	2000
100_5002	2001	3000
Desired Output:

Store	Merch List
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
1	100_5000
.
.
.

[/pre]
Note I already have a solution for the Store List column.

 

[Luke M]

How to post a file:

http://chandoo.org/forums/topic/posting-a-sample-workbook

For your store list, this macro should help you out. Tried to comment it out appropriately.

[pre]

Sub ListText()
'First, let's assume your table Input is in A:C,
'with the data starting in row 2

'Since we're already using columns A:C for input, I'll
'write this macro to output to column E and F

'first, we define some variable
Dim merchName As String
Dim startRow As Long
Dim endRow As Long
Dim rCount As Long
Dim i As Long

'Set intial record count
rCount = 1

'Now, we setup a loop to go through all the Merch_List
For i = 2 To Range("A2").End(xlDown).Row
'Get necessary input info
merchName = Cells(i, "A").Value
startRow = Cells(i, "B").Value
endRow = Cells(i, "C").Value

'Now we can do our output
Range(Cells(startRow, "E"), Cells(endRow, "E")).Value = rCount
Range(Cells(startRow, "F"), Cells(endRow, "F")).Value = merchName

'increment rcount
rCount = rCount + 1

'and we're ready for the next input line
Next i

End Sub

[/pre]

 

[blcexcel]

Excellent! I knew there would be a solution that didn't require a lot of code.


Thank you very much and enjoy the rest of your week!

 

[blcexcel]

I could use a little more help.... I want to have the output on a separate tab. I have the data on "MerchList" and I want the data on "Output". I tried adding two Sheets statements but it doesn't like the row after: Sheets("Output").Select

[pre]

'Start Merch List
Sheets("MerchList").Select
Dim merchName As String
Dim startRow As Long
Dim endRow As Long
Dim rCount As Long
Dim x As Long

'Set intial record count
rCount = 1

'Now, we setup a loop to go through all the Merch_List
For x = 2 To Range("A6").End(xlDown).Row
'Get necessary input info
merchName = Cells(x, "A").Value
startRow = Cells(x, "B").Value
endRow = Cells(x, "C").Value

'Now we can do our output
Sheets("Output").Select
Range(Cells(startRow, "B"), Cells(endRow, "B")).Value = merchName

'increment rcount
rCount = rCount + 1

'and we're ready for the next input line
Next x

[/pre]

 

[Debraj]

Hi Blcexcel..


Little modification in Luke's Code..


change this area..

[pre]

-------------
'Now we can do our output
Range(Cells(startRow, "E"), Cells(endRow, "E")).Value = rCount
Range(Cells(startRow, "F"), Cells(endRow, "F")).Value = merchName
-----------
to this


With Sheets("Output")
'Now we can do our output
.Range(.Cells(startRow, "E"), .Cells(endRow, "E")).Value = rCount
.Range(.Cells(startRow, "F"), .Cells(endRow, "F")).Value = merchName
End With

[/pre]
Regards,

Deb

 

[Luke M]

How's this?

[pre]

Sub ListText()
'First, let's assume your table Input is in A:C,
'with the data starting in row 2

'Since we're already using columns A:C for input, I'll
'write this macro to output to column E and F

'first, we define some variable
Dim merchName As String
Dim startRow As Long
Dim endRow As Long
Dim rCount As Long
Dim i As Long

'Set intial record count
rCount = 1

'Where we want the data to go
Worksheets("Output").Select

'Now, we setup a loop to go through all the Merch_List
'Add a With statement to trace back to where the MerchList is at
With Worksheets("MerchList")
For i = 2 To .Range("A2").End(xlDown).Row
'Get necessary input info from the other sheet
merchName = .Cells(i, "A").Value
startRow = .Cells(i, "B").Value
endRow = .Cells(i, "C").Value

'Now we can do our output
Range(Cells(startRow, "E"), Cells(endRow, "E")).Value = rCount
Range(Cells(startRow, "F"), Cells(endRow, "F")).Value = merchName

'increment rcount
rCount = rCount + 1

'and we're ready for the next input line
Next i
End With

End Sub

[/pre]

 

[Luke M]

Ha ha, nice Deb. We each took the opposite approach. I think I like your way better though.

 

[Debraj]

Ha ha.. trying to play like Ninja..

 

[Debraj]

Blcexcel..


You can try another approach..


In Output sheet.. F6.. write formula as

=INDEX(MerchList!$A$2:$A$4,MATCH(FALSE, COUNTIF(Output!$F$5:F5,MerchList!$A$2:$A$4)=MerchList!$C$2:$C$4,0))

Confirm the formula by Ctrl + Shift + Enter not just Enter


Drag downward..


Regards,

Deb

 

[blcexcel]

Thanks!!!


I want to use the macro approach because the ranges will be changing. And I don't want users to have to adjust the formula range. But I appreciate the extra suggestion!

 

[blcexcel]

This does the trick. But I do want to ask a follow-up question because I'm going to use this code for some different variations of templates. I notice the data input has to be in row 1 for the code to work. If it wouldn't require a significant change I would like to know what it would look like to not have that requirement. Thanks in advance!

 

[Luke M]

The data input table starting location is mentioned in this line:

For i = 2 To .Range("A2").End(xlDown).Row

with 2 being the known start row, and then calculating where the end is. You can change the 2 to whatever the correct row needs to be.

 

[blcexcel]

Well that was easy. Thanks again! You can mark this one as complete!