FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

[mmartens]

This is what I am doing I am importing data from a file to a work sheet which get the name of the file. I want to determine the max value in the colume from that work sheet and put it in the cell of an other work sheet which name stays the same.

EXCEL MACRO

Dim intlastrow As Integer
Dim FirstSheetName As String

' determine the total number of work sheet in the work book
LastWorkSheet = Worksheets.Count
'OPENS_A_DIALOGUE_BOX_TO_OPEN_THE_DATA_FILE()
Application.Dialogs(xlDialogOpen).Show ("*.out&quot
'Move the worksheet to the PipLin Report.xls file into the last worksheet place
FirstSheetName = Sheets(1).Name
Sheets(FirstSheetName).Select
Sheets(FirstSheetName).Move After:=Workbooks("Test Report Round.xls&quot.Sheets(LastWorkSheet)

' Selecting the a column we finded the number of rows in it.
' The last row number is entered into varable name.
Range("A3&quot.Select
Selection.End(xlDown).Select
intlastrow = ActiveCell.Row

' MOVE TO THE INFORMATION SHEET IN ORDER TO PUT IN INFORMATION
' This sheet name stays the same
Sheets("TEST INFORMATION SHEET&quot.Select
Range("D22&quot.Select ' this cell is in the work sheet "TEST INFORMATION SHEET"
ActiveCell.Formula = "=MAX(test!A3:A500)"
'This is the problem line
'(1) I do not know the name of the data sheet EXAMPLE HERE (test)
'(2) I do not know how long colume A is going to be

This is what I have tryed thinking that it would work but it did not

ActiveCell.Formula = " = MAX(INDIRECT(FirstSheetName)"!A3:A" & intlastrow)"

Any help would be great I am at the end of my rope...

 

[Mutt]

Can try something like this :

Sheets(FirstSheetName).Select
Range("a1&quot.Select
ActiveCell.CurrentRegion.Select
list_range = ActiveWindow.RangeSelection.Address

Sheets("TEST INFORMATION SHEET&quot.Select
Range("D22&quot.Formula = "=max(" + FirstSheetName + "!" + list_range + &quot"

 

[mmartens]

Thanks Mutt

It worked great thanks alot

mmartens