Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help to correct code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
  
Posts: 122
Default Help to correct code
Could someone let me know what is wrong with the following two pieces of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate further.

Thanks
Pat


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Help to correct code
I think it might help if you give us a hint as to what is going on or not as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two pieces of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate further.

Thanks
Pat




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
  
Posts: 122
Default Help to correct code
Sorry for the vagueness of my question.

The code is placed in a workbook called PrepareTest.xls

I am trying to filldown the following formulas which are on the first row.
The filling down must stop when there is no more data to display in column A

='[Master.xls]PL'!$D8 contained in A1
='[Master.xls]PL'!$E8 contained in B1
='[Master.xls]PL'!$F8 contained in C1
='[Master.xls]PL'!$G8 contained in D1
='[Master.xls]PL'!$H8 contained in E1
='[Master.xls]PL'!$I8 contained in F1
='[Master.xls]PL'!$J8 contained in G1

Then I want to save the workbook as a .CSV file

Hope you understand what I am trying to do.


"Bob Phillips" wrote in message
...
I think it might help if you give us a hint as to what is going on or not
as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two pieces of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate further.

Thanks
Pat






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
  
Posts: 122
Default Help to correct code
I need to make a correction, instead of Master.xls in each of the formulas
it should be PrepareTest.xls.
Sorry

"Pat" wrote in message
...
Sorry for the vagueness of my question.

The code is placed in a workbook called PrepareTest.xls

I am trying to filldown the following formulas which are on the first row.
The filling down must stop when there is no more data to display in column
A

='[Master.xls]PL'!$D8 contained in A1
='[Master.xls]PL'!$E8 contained in B1
='[Master.xls]PL'!$F8 contained in C1
='[Master.xls]PL'!$G8 contained in D1
='[Master.xls]PL'!$H8 contained in E1
='[Master.xls]PL'!$I8 contained in F1
='[Master.xls]PL'!$J8 contained in G1

Then I want to save the workbook as a .CSV file

Hope you understand what I am trying to do.


"Bob Phillips" wrote in message
...
I think it might help if you give us a hint as to what is going on or
not
as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two pieces
of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate
further.

Thanks
Pat








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
  
Posts: 122
Default Help to correct code
O dear, I think I have eaten too much turkey, please ignore the last post
the original is correct after all.


"Pat" wrote in message
...
I need to make a correction, instead of Master.xls in each of the formulas
it should be PrepareTest.xls.
Sorry

"Pat" wrote in message
...
Sorry for the vagueness of my question.

The code is placed in a workbook called PrepareTest.xls

I am trying to filldown the following formulas which are on the first
row.
The filling down must stop when there is no more data to display in
column
A

='[Master.xls]PL'!$D8 contained in A1
='[Master.xls]PL'!$E8 contained in B1
='[Master.xls]PL'!$F8 contained in C1
='[Master.xls]PL'!$G8 contained in D1
='[Master.xls]PL'!$H8 contained in E1
='[Master.xls]PL'!$I8 contained in F1
='[Master.xls]PL'!$J8 contained in G1

Then I want to save the workbook as a .CSV file

Hope you understand what I am trying to do.


"Bob Phillips" wrote in message
...
I think it might help if you give us a hint as to what is going on or
not
as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two
pieces
of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate
further.

Thanks
Pat










Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Help to correct code
You still didn't explain what went wrong, but when I tried it I got stuck
here

'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row

I don't understand your comment. The syntax is wrong, but I don't know what
to put as I don't know what row value are you trying to get here?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Sorry for the vagueness of my question.

The code is placed in a workbook called PrepareTest.xls

I am trying to filldown the following formulas which are on the first row.
The filling down must stop when there is no more data to display in column
A

='[Master.xls]PL'!$D8 contained in A1
='[Master.xls]PL'!$E8 contained in B1
='[Master.xls]PL'!$F8 contained in C1
='[Master.xls]PL'!$G8 contained in D1
='[Master.xls]PL'!$H8 contained in E1
='[Master.xls]PL'!$I8 contained in F1
='[Master.xls]PL'!$J8 contained in G1

Then I want to save the workbook as a .CSV file

Hope you understand what I am trying to do.


"Bob Phillips" wrote in message
...
I think it might help if you give us a hint as to what is going on or
not
as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two pieces
of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate
further.

Thanks
Pat








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
  
Posts: 122
Default Help to correct code
I got stuck at the same location also, the comment refers to PrepareTest.xls
but as the syntax is wrong please ignore where approbate. I am trying to
get the data from Master.xls into A1



"Bob Phillips" wrote in message
...
You still didn't explain what went wrong, but when I tried it I got stuck
here

'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row

I don't understand your comment. The syntax is wrong, but I don't know
what
to put as I don't know what row value are you trying to get here?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Sorry for the vagueness of my question.

The code is placed in a workbook called PrepareTest.xls

I am trying to filldown the following formulas which are on the first
row.
The filling down must stop when there is no more data to display in
column
A

='[Master.xls]PL'!$D8 contained in A1
='[Master.xls]PL'!$E8 contained in B1
='[Master.xls]PL'!$F8 contained in C1
='[Master.xls]PL'!$G8 contained in D1
='[Master.xls]PL'!$H8 contained in E1
='[Master.xls]PL'!$I8 contained in F1
='[Master.xls]PL'!$J8 contained in G1

Then I want to save the workbook as a .CSV file

Hope you understand what I am trying to do.


"Bob Phillips" wrote in message
...
I think it might help if you give us a hint as to what is going on or
not
as
the case may be, rather than leave us to work it out.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pat" wrote in message
...
Could someone let me know what is wrong with the following two
pieces
of
code?

Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range

'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A :A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select

Calculate

End Sub


Option Explicit

Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow 1
LastDataRow = LastDataRow - 1
Loop
End Function

If it isn't clear what I am trying to achieve I will elaborate
further.

Thanks
Pat










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to search for a code, and have the correct information auto fi Jared Excel Discussion (Misc queries) 4 November 9th 09 05:04 AM
#Value! error on code that should be correct? RHein Excel Discussion (Misc queries) 2 January 3rd 08 03:19 AM
Can you help with the correct translation of this code? Just For Fun... Excel Programming 0 September 30th 04 06:16 AM
I will never, ever get this correct ... Dan E[_2_] Excel Programming 0 September 2nd 03 03:41 PM
I will never, ever get this correct ... Chip Pearson Excel Programming 0 September 2nd 03 03:34 PM


All times are GMT +1. The time now is 12:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement