×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

IMPORTING DATA

IMPORTING DATA

IMPORTING DATA

(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.

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: IMPORTING DATA

Try this:

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

RE: IMPORTING DATA

(OP)
Thanks vhphan

It works great thanks

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!


Resources