The dot is necessary within the WITH statement. If desired, reply to this
with your file with a complete explanation and before/after examples.
Using a worksheet function requires the application but not the worksheet
function. You could have used vba FIND. Closing down now.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-----Original Message-----
From: Domain Admin
Sent: Tuesday, March 27, 2012 6:54 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but
this must be possible
Thanks. Believe me I am a firm believer in KISS. I did not realize
the . made that distinction though.
Also like getting rid of worksheetfunction. I went the other way and
tried to get rid of application and that failed. I thought it was the
redundant one not worksheetfunction.
So for cross referencing can I create a reference
Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for
access since what I will be doing is reading cell values from one
sheet, performing functions on the values then setting cell values in
the other sheet. Or is there more to it than that?
On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 <dguille...@gmail.com> wrote:
Sub InitRefs()
dim fv As long
dim lv As long
With Sheets(“ChartData”)
fv= .Application.Match("Stopval", .Rows(1), 0) ‘notice the dot (.)
before
rows
End With
With Sheets(“Results”)
lv = .Application..Match("ReverseDate", .Rows(1), 0) ‘dot NOT needed on
ACTIVE sheet but needed here.
End With
End Sub
Try to adhere to the KISS principle
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: tangledweb
Sent: Tuesday, March 27, 2012 5:50 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this
must be possible
I have one sheet of raw data with several columns and thousands of rows.
I
need to process this and I wanted to put the results on a separate sheet
with several columns and dozens of rows when done. I saw some example
code
where it seemed you could do this as long as you qualified the sheet path
but doing what I think is the same thing is not working. Also I would
like
to minimize the full paths I have to use or minimize activations when
going
back and forth from one sheet to the other to get data, process, then
store
data which will have to be done thousands and thousands of times. So part
of the question is can you set path references that can be used to
minimize
the typing volume and readability?
But first I need to know why I can not get this most basic initialization
to
work If I interactively select sheet ChartData (it is not an excel chart
just a name) then the first initialization works but not the one for
Results
and visa versa. I have stripped out unnecessary columns for simplicity.
I
tried both with and without activating sheet Results but get the error
"Unable to get results of the match property class" for the ReverseDate
assignment line no matter what I do. Also tried the names with and
without
quotes to no difference.
If in the interface I select sheet Results then the error occurs on the
Stopval assignment line.
' Column reference objects
Public Stopval As Double
'
Public ReverseDate As Double
' Sheet names
Const ChartData As String = "ChartData"
Const Results As String = "Results"
Sub InitRefs()
'
' Macro which sets the data column names and misc values for the sheets
'
With Sheets(ChartData)
Stopval = .Application.WorksheetFunction.Match("Stopval",
Rows("1:1"), 0)
End With
' Sheets("Results").Activate
' With Sheets(Results)
' ReverseDate = .Application.WorksheetFunction.Match("ReverseDate",
Rows("1:1"), 0)
' End With
End Sub
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
