none
use of array to compare cell values and changing another column value based on the match RRS feed

  • Question

  • I have an worksheet consists of Database names and tables details of each Database.

    A2:A5 consists of DB Names.

    A7:A30 are having table details respect to each DB.

    I have made a dynamic array of the DB names  using vbscript. Want to highlight the rows from A7:A30 based on each database names in the array, dynamically. I have written the below code. Though it is not giving me any errors and creates the excel file, it is not highlighting the values. I am not getting where am I doing wrong?

    Const xlDelimited = 1
    Path = "......"
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.OpenText Path & "test.txt", , , xlDelimited, , , , , , , True, "|"
    Set xlWb = xlApp.ActiveWorkbook
    Set xlWs = xlWb.ActiveSheet
    i=2
    x=0
    y=0
    Do Until xlApp.Cells(i, 1).Value = ""
        ReDim arrExcelValues(x)
        arrExcelValues(x) = xlApp.Cells(i, 1).Value
       Redim arrExcelValues1(y)
    arrExcelValues1(y) = xlApp.Cells(i, 3).Value
    i = i + 1
     x = x + 1
    y = y + 1
    loop
    i=i+2

    a=0

    for a = 0 to ubound(arrExcelValues)
    If xlApp.Cells(i,1).Value = arrExcelValues(a) Then
    xlApp.Cells(i,1).Interior.ColorIndex = 6
    end if

    i=i+1

    next

    xlWb.SaveAs Path & "test1.xls", 1
    xlWb.Close
    xlApp.Quit


    Crynet

    • Moved by Bill_Stewart Friday, January 8, 2016 3:07 PM Move to more appropriate forum
    Friday, January 8, 2016 12:02 PM

Answers

  • Your biggest mistake here is to try and say how you want to do this tather than just saying what you want to do.

    The code example I posted will work with a named range very nicely.

    Using the Range.Find method allws you to do this very quickly

    You can even chance doing it this way:
    Enumerate A column to first blank and create named range>
    Enumerate  remainder of column to find end and create named range #2.

    Loop through cells in named range #1 and look upi values in named range #2 using "find".

    Here is a snippet that find the first blank cell in a column.

    When it finishes "cell" is the fist blank. It will work on a sheet or a range:

    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    


    \_(ツ)_/

    Monday, January 11, 2016 1:22 PM

All replies

  • Try it like this to see how to work with ranges,

    Const xlDelimited = 1
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible=True
    
    xlApp.Workbooks.OpenText "c:\test\test.txt", , , xlDelimited, , , , , , , True, "|"
    Set xlWb = xlApp.ActiveWorkbook
    Set xlWs = xlWb.ActiveSheet
    xlWs.UsedRange.Interior.ColorIndex = 6
    xlWb.SaveAs "c:\test\test1.xls", 1
    'xlApp.Quit


    \_(ツ)_/

    Friday, January 8, 2016 4:07 PM
  • Any reason you can't just use conditional formatting?
    Friday, January 8, 2016 4:16 PM
  • I have an worksheet consists of Database names and tables details of each Database.

    A2:A5 consists of DB Names.

    A7:A30 are having table details respect to each DB.

    I have made a dynamic array of the DB names  using vbscript. Want to highlight the rows from A7:A30 based on each database names in the array, dynamically. I have written the below code. Though it is not giving me any errors and creates the excel file, it is not highlighting the values. I am not getting where am I doing wrong?

    I can also say that your question is vague. What are you trying to highlight based on what,? It sounds like you want to "Find" a cell or row based on a cells contents. Is that what you are trying to do?

    You can search a range for a match and select a cell,row or column based on the results of the search.  THre is no need to use arrays.  Excel is one huge array of arrays.


    \_(ツ)_/

    Friday, January 8, 2016 4:24 PM
  • Here is how you cab select a specific range.  Her ei t is adding color but you cand do anything with the cells,rows ofr columns in the range.

    xlWs.Range("A1:A5").Interior.ColorIndex = 6


    \_(ツ)_/

    Friday, January 8, 2016 4:29 PM
  • Here is a simple example of how to find a value in a range.

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible=True
    
    xlApp.Workbooks.OpenText "c:\test\test.txt"
    Set xlWs = xlApp.ActiveSheet
    Set c = xlWs.Range("A1:A30").Find("CoolSense")
    if Not c Is Nothing Then c.Interior.ColorIndex = 6
    'xlWb.SaveAs "c:\test\test1.xls", 1
    'xlApp.Quit
    


    \_(ツ)_/

    Friday, January 8, 2016 4:45 PM
  • Here is an example of how to use one range to find and colorize items inn another range.

    $xl.ActiveSheet.Range('A1:A3').Cells | 
    	%{
    		$color=($xl.ActiveSheet.Cells.Item($_.Row,2)).Text
    		$xl.ActiveSheet.Range('C4:C98').Find($_.Text) | 
    		     %{$_.Cells.interior.color = [System.Drawing.ColorTranslator]::ToOle([System.Drawing.Color]$color)}
        }

    This uses PowerShell to show how much easier things are to do in PowerShell.  It is one simple pipeline.


    \_(ツ)_/


    • Edited by jrv Friday, January 8, 2016 5:52 PM
    Friday, January 8, 2016 5:51 PM
  • Hi crynet,

    I made a simple test with below code under VBA, and it could highlight the cells.

    Sub test()
        Application.Cells(1, 1).Interior.ColorIndex = 5
    End Sub

    I agree with jrv on that your issue might be related with if statement, and I suggest you comment out if statement to check whether cells would be hightlight.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 11, 2016 2:46 AM
  • Hi everybody. Thank you for your suggestions. I felt that I was not able to clearly convey my query. All the codes provided by you are working fine for highlighting a cell. But I was not trying to highlight just a cell. Thus I am trying to explain elaborately by providing the screenshot of the excel file.

    After importing a text file to excel, the worksheet is having the data in the below format. These contents may get changed when the next text file is imported.

    DB Name No. of Tables Storage (KB)
    test1 2 87318834
    test2 5 1154709
    test3 6 170910
    test4 1 887548
    DB Name Table Name Storage (KB)
    test1 table1 87311787
    test1 table2 7047
    test2 table1 364375
    test2 table2 782483
    test2 table3 7048
    test2 table4 739
    test2 table5 64
    test3 table1 109650
    test3 table2 4718
    test3 table3 8607
    test3 table4 11365
    test3 table5 15566
    test3 table6 21004
    test4 table1 887548

    As all the values are dynamic, I can't provide a definite range.

    Here A2 to A5, only Database details are available.

    And from A8 table details per database is given. (Database "test1" is having two Tables "table 1" & "table2")

    I want to highlight the values starting from C8 with respect to each Database starting from A2 till it encounters a blank cell (here it is A6).

    For example, C8 & C9 needs to be highlighted if the corresponding A8 & A9 values match with A2.

    C10 to C14 needs to be highlighted if the corresponding A10 & A14 values match with A3. And so on.

    As the task required to be completed automatically and the values are all dynamic, I have created the array of Database names till it encounters a blank cell. and was trying to compare the Array values with the values from A8.


    Crynet

    Monday, January 11, 2016 8:46 AM
  • Some suggestions tomake you needs work:

    #1 Import files into separate worksheets and reference them by "UsedRange".
    or
    #2 Import files into a named range and get the range by referencing the named range.


    \_(ツ)_/

    Monday, January 11, 2016 1:10 PM
  • Your biggest mistake here is to try and say how you want to do this tather than just saying what you want to do.

    The code example I posted will work with a named range very nicely.

    Using the Range.Find method allws you to do this very quickly

    You can even chance doing it this way:
    Enumerate A column to first blank and create named range>
    Enumerate  remainder of column to find end and create named range #2.

    Loop through cells in named range #1 and look upi values in named range #2 using "find".

    Here is a snippet that find the first blank cell in a column.

    When it finishes "cell" is the fist blank. It will work on a sheet or a range:

    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    


    \_(ツ)_/

    Monday, January 11, 2016 1:22 PM