Ø Loop While Not c Is Nothing And c.Address <> firstaddress
Will generate an error if c is nothing, because it will still try to
evaluate c.Address.
However, without studying the code too closely: I don't think c ever will
be Nothing at that point, since .FindNext always loops around again, and the
Do..Loop was only used at all if something was found. So, this line should
work just as well :
Loop While c.Address <> firstaddress
If you really do have a possible condition of c Is Nothing, then you would
need to move this logic to prior lines of code and set a flag to test for
the loop, so you can only evaluate c.Address if appropriate. Such as:
StillSearching = False
If Not c Is Nothing Then If c.Address <> firstaddress Then
StillSearching = True
Loop While StillSearching
Also, since Find always starts searching AFTER a specified cell in the
range, or after the top left cell, if the value you are looking for is in
that first cell, your routine will find that first value last. If
undesirable, one way to workaround is to specify the last cell in the range
for the "after" argument to Find.
Set c = .Find("LedgerTotal", After:=.Cells(.Rows.Count, .Columns.Count),
LookIn:=xlValues)
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Saturday, February 11, 2012 8:11 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ formula for this
I should have removed or commented out this line.
MsgBox c.Offset(, 6)
Please use this instead
Option Explicit
Sub findtotalsSAS()
Dim c As Range
Dim firstaddress As String
With Sheets("sheet1").Range("f1:f150")
Set c = .Find("LedgerTotal", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
‘====commented out
‘ MsgBox c.Offset(, 6)
‘=======
Sheets("sheet2").Cells(Rows.Count, "b").End(xlUp)(2).Value = _
c.Offset(, 6).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
From: Lakshman Prasad <mailto:lakshman...@yahoo.com>
Sent: Friday, February 10, 2012 11:20 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ formula for this
Dear Sir,
Running problem plz see the attached screenshot
From: dguillett1 <dguille...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Friday, 10 February 2012 8:02 PM
Subject: Re: $$Excel-Macros$$ formula for this
Or,
‘==========
Option Explicit
Sub findtotalsSAS()
Dim c As Range
Dim firstaddress As String
With Sheets("sheet1").Range("f1:f150")
Set c = .Find("LedgerTotal", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
‘ MsgBox c.Offset(, 6)
Sheets("sheet2").Cells(Rows.Count, "b").End(xlUp)(2).Value = _
c.Offset(, 6).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End Sub
‘==========
Don Guillett
SalesAid Software
dguille...@gmail.com
From: David Grugeon <mailto:grug...@gmail.com>
Sent: Friday, February 10, 2012 6:58 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ formula for this
Try the attached
On 10 February 2012 21:50, Lakshman Prasad <lakshman...@yahoo.com> wrote:
m waiting
From: Lakshman Prasad <lakshman...@yahoo.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Cc: "excelexpertsc...@yahoogroups.com" <excelexpertsc...@yahoogroups.com>
Sent: Friday, 10 February 2012 3:01 PM
Subject: $$Excel-Macros$$ formula for this
Dear Group member,
Plz see the attached file, I have a workbook (sheet1) that have some raw
date, I want to arrange as my requirement like sheet2.
Is that any VBA code or formula for this.
Regards
LAKSHMAN
--
--
David Grugeon
--
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
--
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
