Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 05:59 PM   #1
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Find Cell Value. and perform Loop

Hi Forum,

I need to check records in an Excel 2016 file - Column D.
If this is "X" Then I need to save the Cell Value Offset -4 - Column A to Variable "SORef"
Then I need to Loop through all Rows in Column A with the same "SORef and change the Cell Value for Column D to "X"
This may mean checking Up and Down.

Should be simple but I am new to Excel VBA and cannot seem to get this started

Appreciate any support.
Column is

PNGBill is offline   Reply With Quote
Old 12-08-2018, 12:25 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

I often 'get started' with Excel VBA coding by macro recording. Perform steps manually then modify the generated code as needed. I used recorder to get the Find syntax then figured out the rest. So maybe like:
Code:
Sub Macro1()
Dim SORef As String, intRow As Integer
Cells.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
SORef = ActiveCell.Offset(0, -3)
With Worksheets("Sheet1")
    intRow = 1
    While Not IsEmpty(.Cells(intRow, 1))
        If .Cells(intRow, 1) = SORef Then .Cells(intRow, 4) = "X"
        intRow = intRow + 1
    Wend
End With
End Sub
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

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

Thanks June7,
Was hoping for some basic code examples and then I can take it from there.

Became skilled at Access VBA although not used same for 5 years
but Excel has different commands.

PNGBill is offline   Reply With Quote
Old 12-08-2018, 12:55 PM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,605
Thanks: 388
Thanked 622 Times in 603 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Find Cell Value. and perform Loop

Not that simple.?
Eg row D2 has an X in it.? A2 has a value of D2 let's say.
You then filter column A for D2 and then change column D to X for the filtered set

Now you unfilter and look for an X in column D ?

How do you know when you have finished.?
How do you avoid the rows already processed.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
PNGBill (12-11-2018)
Old 12-08-2018, 01:00 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Find Cell Value. and perform Loop

would think something like this
Code:
 
dim I as integer
 
for I=2 to maxrows
     if cells(I,1)=SORef then cells(I,4)="X"
next i
you need to determine maxrows, but you have not provided any information on where your data is or how it is structured, so assuming it is a table format with no blank rows or columns, with headers in row 1 and data below, it can be determined using currentregion

maxrows=range("A1").currentregion.rows
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
PNGBill (12-11-2018)
Old 12-08-2018, 01:03 PM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

I edited my previous post after you read it.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 12-11-2018, 03:07 PM   #7
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Here is my code so far.

It works to find the Rows with "x" in Column 5 - E.
It appears to identify the Value in Column 1 - A for this Row.

I have inserted i2 to then process the worksheet to change the value in E where the value of A is SORef.

Of the 20 records it should have added x to all rows after finding x


Appreciate any support.

Code:
Sub IdentifyInstalls()

' Get the last row with text
    Dim shNames As Worksheet
    Set shNames = ThisWorkbook.Worksheets("Sheet1")
    Dim StartRow As Long
    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    StartRow = 2
    
    'Temp Last Row for Testing Purposes
    LastRow = 20
            
    'Check for "x" in Column E
    Dim i As Long
    Dim i2 As Long
    Dim SORef As Long
            
    'Go through Rows and check for "x"
    'For i = StartRow To LastRow
   
    For i = StartRow To LastRow Step 1
        If Sheet1.Cells(i, 5).Value = "x" Then
            SORef = ActiveCell.Offset(i, -4)
                For i2 = StartRow To LastRow Step 1
                If Sheet1.Cells(i2, 1).Value = SORef Then Sheet1.Cells(i2, 1).Offset(0, 4).Value2 = "x"
                Next i2
        End If
    Next
      Debug.Print "Task Complete"
End Sub

PNGBill is offline   Reply With Quote
Old 12-11-2018, 03:31 PM   #8
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

Not clear to me what issue is. Does the code error, produce wrong results, do nothing?

Is Value2 a valid property?

Could you provide your file? Follow instructions at bottom of my post.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 12-11-2018, 04:01 PM   #9
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

File attached
Sales Statistics Copy (2).zip
PNGBill is offline   Reply With Quote
Old 12-11-2018, 04:03 PM   #10
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Macro is IdentifyInstalls()
PNGBill is offline   Reply With Quote
Old 12-11-2018, 10:12 PM   #11
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

I have the file. Not clear to me what you actually want to happen. The worksheet structure doesn't seem to agree with description in original post. Column D does not have "X", Column E does. And X is associated with multiple different Order Number in Column A. Which Order Number should be used? Sorry, this is not making sense.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 12-11-2018, 10:21 PM   #12
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Thanks June7,
Some records in the last column have an x.
Where a row has an x. All Rows with the same reference in column A also need to have an x.
This is Sheet1.
Trust this helps and many thanks for your assistance.
However we need all of the rows that have an x in one or more
PNGBill is offline   Reply With Quote
Old 12-12-2018, 12:09 AM   #13
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,849
Thanks: 0
Thanked 449 Times in 445 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Find Cell Value. and perform Loop

Okay, here's what I did. In VBA Editor, set Reference to Microsoft ActiveX Data Objects x.x Library because code is using early binding.
Code:
Sub setX()
Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim intRow As Integer
    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    'open the recordset
    rst.Open "SELECT DISTINCT [Order Number] FROM [Sheet1$] WHERE [Line Note]='x'", cnx, adOpenDynamic, adLockOptimistic
    While Not rst.EOF
        intRow = 1
        With Worksheets("Sheet1")
            While Not IsEmpty(.Cells(intRow, 1))
                If .Cells(intRow, 1) = rst![Order Number] Then .Cells(intRow, 5) = "x"
                intRow = intRow + 1
            Wend
        End With
        rst.MoveNext
    Wend
    'disconnect the recordset
    rst.Close
    Set rst.ActiveConnection = Nothing
    'cleanup
    If CBool(cnx.State And adStateOpen) = True Then cnx.Close
    Set cnx = Nothing
End Sub
Or use this for the recordset loop:
Code:
With Worksheets("Sheet1")
    While Not rst.EOF
        .Range("A1").AutoFilter Field:=1, Criteria1:=rst![Order Number]
        .Columns(5).Cells.Replace What:="", Replacement:="x"
        rst.MoveNext
    Wend
    .ShowAllData
End With
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 12-12-2018 at 11:49 AM.
June7 is offline   Reply With Quote
Old 12-12-2018, 12:29 AM   #14
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Thanks again June7.

I don't have ActiveX Data Objects x.x Library
2.0, 2.1, 2.5, 2.6, 2.7, 2.8, 6.1 plus ..Objects Recordset 2.8 Library & 6.0 Library
PNGBill is offline   Reply With Quote
Old 12-12-2018, 12:47 AM   #15
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,264
Thanks: 71
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Find Cell Value. and perform Loop

Just realised my Software details were out of date.
Now using Win 10 with Office 2016.

PNGBill is offline   Reply With Quote
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:42 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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World