×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

(OP)
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")
'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").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").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").Select
Range("D22").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...

RE: FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

Can try something like this :

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

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

RE: FORMULA ERROR WITH UNKNOWN SHEET NAME AND COLUME SIZE

(OP)
Thanks Mutt

It worked great thanks alot

mmartens

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close