Solved

Need Excel VBA Code to parse a short string of values from one column conditionally into other columns based on string value

Posted on 2015-02-16
8
389 Views
Last Modified: 2016-02-11
Using:  Excel 2010

I have a large worksheet (see attached excerpt) with strings in one column which look like:

1,2,3
0,1,4
5,6,7,8

Let's assume these strings define whether some modification needs to be applied to a particular building number like B0, B1, through B8.

I'm needing to apply a 'Y' (for 'Yes') into a corresponding column (there is a column for B0 through B8) which will make it easier to subsequently to sorts on those columns by building number.

I'm not familiar enough with VBA yet to write this code quickly and could use some help.

Thx


A look at the attached spreadsheet sample should make the above goal/need clear.
Conditional-Parser-VBA-Needed.xls
0
Comment
Question by:qeng
  • 4
  • 4
8 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 375 total points
ID: 40613264
Hi, this procedure should work for you.

Regards,

Rob.

Sub ParseValues()
    For intRow = 8 To Cells(65536, "A").End(xlUp).Row
        strValue = Trim(Cells(intRow, "A").Value)
        If strValue <> "" Then
            arrCols = Split(strValue, ",")
            For Each intCol In arrCols
                intCol = Int(intCol)
                Cells(intRow, intCol + 2).Value = "Y"
            Next
        Else
            For intCol = 0 To 8
                Cells(intRow, intCol + 2).Value = ""
            Next
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:qeng
ID: 40613295
Thanks so much Rob.  Worked like a charm (saved me hours of work).

How would I modify this script to be used in workbook with many worksheets with this same layout (the source string column is always followed by the target 8 columns) but request the user to click on the starting cell.

This arrangement (string in one column, followed by the 8 target columns) is common to many of the worksheets but the starting column (which I made as 'A' in the excerpt I attached previously) could be column G in one worksheet or column X in another depending on which columns users may have added to a particular worksheet.

Maybe some sort of a 'Select Range to Parse' message to the user which requires the user to go to the desired worksheet (which may not be the active one from which the Sub ParseValues() is called) and click-drag the range to parse (probably a good idea to add a 'range confirmation' request to the user also, prior triggering execution, so it doesn't start parsing on the wrong range).

Thx.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 375 total points
ID: 40613339
OK, see how this goes for you.  Note that you need to select the cells in the Building column for this to work.

Sub ParseValues()
    Set MyRange = Selection
    intReturn = MsgBox("Are you sure you want to run the parser on the selected range of " & Replace(MyRange.Address, "$", "") & "?", vbYesNo)
    If intReturn = vbYes Then
        For Each objCell In MyRange
            strValue = Trim(objCell.Value)
            If strValue <> "" Then
                arrCols = Split(strValue, ",")
                For Each intCol In arrCols
                    intCol = Int(intCol)
                    objCell.Offset(0, intCol + 1).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol + 1).Value = ""
                Next
            End If
        Next
    End If
End Sub

Open in new window


Regards,

Rob.
0
 

Assisted Solution

by:qeng
qeng earned 0 total points
ID: 40623586
Hey Rob,

Either I'm doing something wrong in how I'm selecting the Building Range (I've tried it a few different ways) or we need an offset to the right by 1.

I first select the range to parse immediately under the Table Heading (in our case from A8:A205) then when I run the second VBA script you gave me, what ends up happening is it parses the text in A8:A205 but it parses it one column to the left of where it should go.  

So what happens is no data appears in the B8 column, the B8 data is instead in the B7 column, and the data which should have gone into the B0 column ends up overwriting the original text string in the A column (this text string should remain intact).

Looks like I was able to fix it by changing:

If strValue <> "" Then
                ...
                    objCell.Offset(0, intCol).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol).Value = ""
                Next
            End If


to:

If strValue <> "" Then
               ...
                    objCell.Offset(0, intCol + 1).Value = "Y"
                Next
            Else
                For intCol = 0 To 8
                    objCell.Offset(0, intCol + 1).Value = ""
                Next
            End If

Was this the correct way to fix it?  It works but I wanted to check just in case there is a more correct way.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 375 total points
ID: 40623946
Hi qeng, you are exactly right.  My apologies, I didn't notice I had forgotten to increment the offset, because your values start at zero.

That should work fine for you.

FYI, I have updated my code snippet to reflect your modification.

Regards,

Rob.
0
 

Author Comment

by:qeng
ID: 40624348
All good Rob.  Thanks a lot for your help.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40624577
No problem. Glad to help.

Rob.
0
 

Author Closing Comment

by:qeng
ID: 40641455
Great support.  Rob was quick and stayed tuned in through the end.  Many thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now