fix code written in access vba to manipulation excel file

drtopserv
drtopserv used Ask the Experts™
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlRange As Object
Dim cell As Object
Dim val As Variant
Dim adrs As String
 
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(sFile)
 
       Set xlSheet = xlBook.Sheets(5)
        xlApp.Visible = True
        Set rsCurr = CurrentDb.QueryDefs("qtblDebitBranchsReportingReport").OpenRecordset
        xlSheet.Range("B12").CopyFromRecordset rsCurr
        xlSheet.Range("A7").CopyFromRecordset rsCurr
 
        Dim R As Long, ColorValue As Long


            For R = 2 To xlSheet.Application.cells(xlBook.Sheets(5).Rows.Count, "C").End(xlUp).Row

                If xlSheet.Application.cells(R, "C").Value Like "*something*" Or xlSheet.Application.cells(R, "C").Value Like "*something2*" Then
                    xlBook.Sheets(5).Rows(R).Interior.Color = 1763209
                End If
            Next
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
the bold line doesnt give me total rows .
note: the excel file should be opened from access and the "sheet5" is hidden in that excel file .
          the activesheet is sheet1 when the files opened by access.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Replace the existing For loop with the following one and see if that works for you.

For R = 2 To xlSheet.cells(xlSheet.rows.Count, "C").End(3)(1).row
    If xlSheet.cells(R, "C").Value Like "*something*" Or xlSheet.cells(R, "C").Value Like "*something2*" Then
        xlSheet.rows(R).Interior.Color = 1763209
    End If
Next

Open in new window


Or change it like this using the enumeration constant...
For R = 2 To xlSheet.cells(xlSheet.rows.Count, "C").End(-4162).row

Open in new window

Where -4162 is equivalent to xlUp.
it works..but i dont know what excel count?
how can do it to count from A12 until the end?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

the excel sheet 5 , has hundreds of rows , and it counts only 15 rows...
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Right now it is counting the last row with data in Column C on the Sheet with index#5 in the Workbook.

If you want to count the last row with data in Column A and start the loop from the Row#12, you should try this...

Dim LR As Long
'Finding last row with data in Column A
LR = xlSheet.cells(xlSheet.rows.Count, "A").End(3)(1).row

'Looping through Row#12 to the last Row with data in Column A
For R = 12 To LR
    If xlSheet.cells(R, "C").Value Like "*something*" Or xlSheet.cells(R, "C").Value Like "*something2*" Then
        xlSheet.rows(R).Interior.Color = 1763209
    End If
Next

Open in new window

WOW MAN !!
Great ,,,it `s working!

please may help me abit :
If xlSheet.cells(R, "C").Value Like "*something*" Or xlSheet.cells(R, "C").Value Like "*something2*" Then
THE "C" in cells in above line is merge with D
i want to search "something" into that merged range..
how can i change "C" in cells(R,"C") to merged cell , like cells(R,"C:D") something like this:{
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
That condition will test the values in column C even if column C is merged with column D.
Isn't it doing the same?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Also when you have the condition If xlSheet.cells(R, "C").Value Like "*something*", the OR condition (xlSheet.cells(R, "C").Value Like "*something2*") is not required as the first condition "*something*" is also qualified for the condition "*something2*".
Yes I know. The "something" is just sample .
The main problem is that C and S merge. When I seek in the merged string I cant find the string.when I unmerge I can find it
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
As I said earlier, as far as column C is the first column in the merged columns whether C:D are merged or C:S are merged, the condition will work correctly.

Please note the Text comparison is case sensitive, change the condition like below and see if that works for you...

If LCase(xlSheet.cells(R, "C").Value) Like "*something*" Or LCase(xlSheet.cells(R, "C").Value) Like "*something2*" Then

In this case, make sure that you mention the criteria in small case.

If still it doesn't work, please upload a sample Excel file to know why doesn't it work.
here is the file.
still can`t find the string in the merged L:M field.
OHH..seems the problem is that the sheet i want to search in is hidden! and the "L" column or cell is not selected.
this is the file:
the sheet i wanna search in is hidden (dat) sheet
SAMPLE.xls
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
There are only two sheets in the sample file you attached but assuming that in your actual file, you have a sheet with index#5 so that the the worksheet variable is set correctly after the line...
Set xlSheet = xlBook.Sheets(5)

The following code works on your sample file without an issue.

Dim LR As Long
'Finding last row with data in Column A
LR = xlSheet.cells(xlSheet.rows.Count, "A").End(3)(1).row

'Looping through Row#12 to the last Row with data in Column A
For R = 12 To LR
    If LCase(xlSheet.cells(R, "L").Value) Like "*something*" Or LCase(xlSheet.cells(R, "L").Value) Like "*something2*" Then
        xlSheet.rows(R).Interior.Color = 1763209
    End If
Next

Open in new window


Now, the above code is looking at the column L on the hidden sheet and if it meets the criteria in a row in column L, the code will change the interior color of that row. Earlier code was not working because it was looking at column C.

Actually I am confused as I am not sure which column you want to search.
But if it is column L (where column L is merged with column M), the above code will work without an issue.
If it is another column in your actual file, just change the column letter in the IF condition within the For loop.
You are genius!
THANKS ALOT!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
You're welcome! Glad it worked as desired in the end.
Thanks for the feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial