Need help with creating an array for a macro

[Shipwreck]

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?

 

[VoG]

This is unclear. What are you actually trying to do (in English words, not code)?

 

[Shipwreck]

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?

 

[VoG]

Do you mean that you want to autofill A from the last row in A to the lastbrow in L?

 

[Shipwreck]

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.

 

[VoG]

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?

 

[Shipwreck]

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.

 

[Shipwreck]

Correction, yes - will be autofilling from the last row A50:L50 up to an unknown row A?

 

[Shipwreck]

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

 

[Shipwreck]

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