Need help with creating an array for a macro

Shipwreck

New Member
Joined
Jul 28, 2010
Messages
10
How do I get an array to store data from a text file to be used for manipulating a row number from in the following code?

textFile = "C:\row.txt"

Selection.AutoFill Destination:=Range("A?:L50"), Type:=xlFillDefault
Range("A?:L50").Select

Has anyone ever perform this function before?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
This is unclear. What are you actually trying to do (in English words, not code)?
 
Upvote 0
I need to change the value of a row number that is unknown every time this macro will be ran.
As per this example: when reading the text file which contains the value for the row A23 will need to be used as below. The Column names "A" and "L50" will always remain the those value, but the number lagging the Column "A?" will always be different.

Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A23:L50"), Type:=xlFillDefault
Range("A23:L50").Select

Is this any help in understanding what I'm trying to do?
 
Upvote 0
Do you mean that you want to autofill A from the last row in A to the lastbrow in L?
 
Upvote 0
The autofill works correctly when using hardcode values, but the row number in place of 23 will need to change dynamically baesd on a value that will be provided from a text file.
 
Upvote 0
You are making this more difficult for both of us by not answering my questions!

This is my final try. Do you have the last row stored as a variable?
 
Upvote 0
The last row will always be row 50, but the row preceeding row 50 is unknown until other code is execute which will dump that value to a text file. Row A50:L50 will be selected and a drag up function will need to perform the autofill function based on the row number that is provided in the text file. Which is why I need to only manipluate the value of "?" that I had initially posted. The "?" mark will be the same value on both lines. Just have been able to figure out correct syntax for inserting the array for these lines.

Selection.AutoFill Destination:=Range("A?"):L50"), Type:=xlFillDefault
Range("A?:L50").Select

? value will be provided in the text file called, "Row.txt"

So the answer to the last row being stored as a variable, the answer is no.
 
Last edited:
Upvote 0
Anyone know how to manipulate the value 23 in the following code, based on reading a text file to be used as an array for substituting row value?

Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A23:L50"), Type:=xlFillDefault
Range("A23:L50").Select
 
Upvote 0
Re: Answer to - Need help with creating an array for a macro

After banging my head for awhile.. I decided to just use a workaround as a bandaid which works.. rather pleasantly! The following does the trick!

Dim textFile As String
Dim MyRow As String
Dim filenum As Integer

textFile = "C:\LSM\row.txt"
filenum = FreeFile()
Open textFile For Input As #filenum
Do While Not EOF(filenum)
Line Input #filenum, MyRow
Windows("File.xlsx").Activate
Sheets("Tab Name").Select

If MyRow = 3 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A3:L50"), Type:=xlFillDefault
Range("A3:L50").Select

ElseIf MyRow = 4 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A4:L50"), Type:=xlFillDefault
Range("A4:L50").Select
ElseIf MyRow = 5 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A5:L50"), Type:=xlFillDefault
Range("A5:L50").Select
ElseIf MyRow = 6 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A6:L50"), Type:=xlFillDefault
Range("A6:L50").Select
ElseIf MyRow = 7 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A7:L50"), Type:=xlFillDefault
Range("A7:L50").Select
ElseIf MyRow = 8 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A8:L50"), Type:=xlFillDefault
Range("A8:L50").Select
ElseIf MyRow = 9 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A9:L50"), Type:=xlFillDefault
Range("A9:L50").Select
ElseIf MyRow = 10 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A10:L50"), Type:=xlFillDefault
Range("A10:L50").Select
ElseIf MyRow = 11 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A11:L50"), Type:=xlFillDefault
Range("A11:L50").Select
ElseIf MyRow = 12 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A12:L50"), Type:=xlFillDefault
Range("A12:L50").Select
ElseIf MyRow = 13 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A13:L50"), Type:=xlFillDefault
Range("A13:L50").Select

ElseIf MyRow = 14 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A14:L50"), Type:=xlFillDefault
Range("A14:L50").Select
ElseIf MyRow = 15 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A15:L50"), Type:=xlFillDefault
Range("A15:L50").Select
ElseIf MyRow = 16 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A16:L50"), Type:=xlFillDefault
Range("A16:L50").Select
ElseIf MyRow = 17 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A17:L50"), Type:=xlFillDefault
Range("A17:L50").Select
ElseIf MyRow = 18 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A18:L50"), Type:=xlFillDefault
Range("A18:L50").Select
ElseIf MyRow = 19 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A19:L50"), Type:=xlFillDefault
Range("A19:L50").Select
ElseIf MyRow = 20 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A20:L50"), Type:=xlFillDefault
Range("A20:L50").Select
ElseIf MyRow = 21 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A21:L50"), Type:=xlFillDefault
Range("A21:L50").Select
ElseIf MyRow = 22 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A22:L50"), Type:=xlFillDefault
Range("A22:L50").Select
ElseIf MyRow = 23 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A23:L50"), Type:=xlFillDefault
Range("A23:L50").Select
ElseIf MyRow = 24 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A24:L50"), Type:=xlFillDefault
Range("A24:L50").Select
ElseIf MyRow = 25 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A25:L50"), Type:=xlFillDefault
Range("A25:L50").Select
ElseIf MyRow = 26 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A26:L50"), Type:=xlFillDefault
Range("A26:L50").Select
ElseIf MyRow = 27 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A27:L50"), Type:=xlFillDefault
Range("A27:L50").Select
ElseIf MyRow = 28 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A28:L50"), Type:=xlFillDefault
Range("A28:L50").Select
ElseIf MyRow = 29 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A29:L50"), Type:=xlFillDefault
Range("A29:L50").Select
ElseIf MyRow = 30 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A30:L50"), Type:=xlFillDefault
Range("A30:L50").Select
ElseIf MyRow = 31 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A31:L50"), Type:=xlFillDefault
Range("A31:L50").Select

ElseIf MyRow = 32 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A32:L50"), Type:=xlFillDefault
Range("A32:L50").Select
ElseIf MyRow = 33 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A33:L50"), Type:=xlFillDefault
Range("A33:L50").Select
ElseIf MyRow = 34 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A34:L50"), Type:=xlFillDefault
Range("A34:L50").Select
ElseIf MyRow = 35 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A35:L50"), Type:=xlFillDefault
Range("A35:L50").Select
ElseIf MyRow = 36 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A36:L50"), Type:=xlFillDefault
Range("A36:L50").Select
ElseIf MyRow = 37 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A37:L50"), Type:=xlFillDefault
Range("A37:L50").Select
ElseIf MyRow = 38 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A38:L50"), Type:=xlFillDefault
Range("A38:L50").Select
ElseIf MyRow = 39 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A39:L50"), Type:=xlFillDefault
Range("A39:L50").Select
ElseIf MyRow = 40 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A40:L50"), Type:=xlFillDefault
Range("A40:L50").Select
ElseIf MyRow = 41 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A41:L50"), Type:=xlFillDefault
Range("A41:L50").Select
ElseIf MyRow = 42 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A42:L50"), Type:=xlFillDefault
Range("A42:L50").Select
ElseIf MyRow = 43 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A43:L50"), Type:=xlFillDefault
Range("A43:L50").Select
ElseIf MyRow = 44 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A44:L50"), Type:=xlFillDefault
Range("A44:L50").Select
ElseIf MyRow = 45 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A45:L50"), Type:=xlFillDefault
Range("A45:L50").Select
ElseIf MyRow = 46 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A46:L50"), Type:=xlFillDefault
Range("A46:L50").Select
ElseIf MyRow = 47 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A47:L50"), Type:=xlFillDefault
Range("A47:L50").Select
ElseIf MyRow = 48 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A48:L50"), Type:=xlFillDefault
Range("A48:L50").Select

ElseIf MyRow = 49 Then
Range("A50:L50").Select
Selection.AutoFill Destination:=Range("A49:L50"), Type:=xlFillDefault
Range("A49:L50").Select
End If
Loop
Close #filenum
 
Upvote 0

Forum statistics

Threads
1,218,811
Messages
6,144,611
Members
450,559
Latest member
kwenda farai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top