Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-12-2018, 01:00 AM   #16
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 960
Thanks: 0
Thanked 217 Times in 217 Posts
June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

I use Win 10 and Office 2010. The code works. I even edited my post with another variation.


x.x represents the version number. You should choose 6.1


Otherwise, try late binding.

Dim rst As Object
Dim cnx As Object
Dim cmd As Object

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 12-12-2018 at 10:37 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
PNGBill (12-12-2018)
Old 12-12-2018, 09:28 AM   #17
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,263
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Thanks again June7.
Just got up and will try again in a hour or so when at work.

I was thinking...
Should the Sheet be Sorted first ?
Sort by A Descending then E Descending.
Run through the records and where an x is found, check the next record and if value in A is the same, then x E, check next record for same value.
When the next record is not the same, loop back to the original check for x for that row.
This way the rows will be run through once only and in one direction.

Appreciate the amount of work you have done and it looks like I have a lot to learn
PNGBill is offline   Reply With Quote
Old 12-12-2018, 10:31 AM   #18
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 960
Thanks: 0
Thanked 217 Times in 217 Posts
June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

Both versions of code open a recordset of Order Numbers where E is x. Then looping through the recordset first method looks for match on each row to the Order Number in the recordset and sets E to x only if match found. The second version filters rows to display only those that match Order Number then sets all E to x.

I would still use recordset even if sorting were applied. Code could find first occurrence of the Order Number, activate that cell, then compare value on each row from that point to the recordset field and if match set E to x, and if no match exit worksheet loop. Need more code rewrite for this. I expect this would perform faster than first option but not faster than the filter option.

I can see that the first option would be far too slow on large dataset but the filter (or rewrite for sorting) should be fast enough since both would only touch on records that actually meet the criteria.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 12-12-2018 at 06:18 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
PNGBill (12-12-2018)
Old 12-12-2018, 05:24 PM   #19
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,263
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Thanks June7,

Your help is much appreciated.
The Project was never going to be easy as the Raw Data was not good but at least we have a result better than guess work.

The Data is nearly 70,000 rows but we do not have to run every week. Maybe once or twice a year.
PNGBill is offline   Reply With Quote
Old 12-17-2018, 08:07 PM   #20
Darrell
Newly Registered User
 
Darrell's Avatar
 
Join Date: Feb 2001
Location: New Zealand
Posts: 83
Thanks: 1
Thanked 1 Time in 1 Post
Darrell
Re: Find Cell Value. and perform Loop

In post #7 you have this:

Code:
SORef = ActiveCell.Offset(i, -4)
But you've not Activated the sheet anywhere so I don't know how you were getting a value for SORef

I would change this section to:

Code:
    'Go through Rows and check for "x"
    With Sheet1
        For i = StartRow To LastRow
            If LCase(.Cells(i, 5)) = "x" Then
                SORef = .Cells(i, 1).Value
                For i2 = StartRow To LastRow
                    If .Cells(i2, 1).Value = SORef Then
                        .Cells(i2, 5) = "x"
                    End If
                Next i2
            End If
        Next i
    End With
With 70k rows though this type of looping may take a long time so you may want to either look at marking rows that have had their value changed so that they don't have to have it changed every loop or doing it completely differently altogether

__________________
Build a man a fire and you keep him warm for a day.
Set the man on fire and you keep him warm for the rest of his life...
Darrell is offline   Reply With Quote
The Following User Says Thank You to Darrell For This Useful Post:
PNGBill (Yesterday)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
find matching cell and goto sln8458 Excel 1 08-06-2015 02:20 AM
find next used cell after blank cell smiler44 Excel 4 10-01-2013 09:15 AM
Find a cell value from range and cell name tim91700 Excel 7 07-29-2013 12:43 AM
problem with Find next matching cell smiler44 Excel 6 02-18-2009 03:49 PM
Edit Cell & Print Loop Dragonchaser Excel 1 11-22-2007 02:47 AM




All times are GMT -8. The time now is 05:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World