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
390 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

803 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