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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,873
Messages
5,513,920
Members
408,977
Latest member
Cattle

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top