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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This is unclear. What are you actually trying to do (in English words, not code)?
 

Shipwreck

New Member
Joined
Jul 28, 2010
Messages
10
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Do you mean that you want to autofill A from the last row in A to the lastbrow in L?
 

Shipwreck

New Member
Joined
Jul 28, 2010
Messages
10

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Jul 28, 2010
Messages
10

ADVERTISEMENT

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:

Shipwreck

New Member
Joined
Jul 28, 2010
Messages
10
Correction, yes - will be autofilling from the last row A50:L50 up to an unknown row A?
 

Shipwreck

New Member
Joined
Jul 28, 2010
Messages
10
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

New Member
Joined
Jul 28, 2010
Messages
10
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,523
Messages
5,837,845
Members
430,518
Latest member
smithk901

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
Top