Excel VBA - Create Multiple Columns on Sheet2 from 1 column on Sheet1 with logic

Question

I am new to VBA and i am struggling trying to make this work. I am in need of a macro that will process each cell\Column on Sheet1 and Put the Results on Sheet2. I'm sure this is pretty easy for those who are more advanced with VB code. It contains many columns..

Anytime we encounter a "—" or an empty cell we populate the cell with -999 see the example on Sheet2. On the first column1 which contains "0-2" then we create two column and populate it with 0 on column1 and 2 on column2 see example on Sheet2 and if a value is found on only once side we then populate both sides with the same number.

As each column is being process in Sheet1 and

Sheet1
++++++
   A              B
Column1         Column2
Title           Title2
0–2               0–4
3                 —
—                 5
—                 
—                 —
10–23             11—29

And the results should look like this on Sheet2

Sheet2
+++++

   A        B            C            D
Column1  Column1      Column2      Column2   
Title-A  Title-B      Title-A      Title-B
0          2         0            4
3          3         -999         -999
-999       -999       5           5
-999       -999       -999       -999
—999       -999       -999       -999
10         23         11           29

Thank you in advance!

Answer 1

You don't really need VBA to do this. It can all be done using the if function.

Column 1 would use something like:

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,LEFT(Sheet1!A2,1)))

Column 2 would use something like:

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,RIGHT(Sheet1!A2,1)))

3 and 4 would use the same as 1 and 2 just shifted over a column.

Answer 2

Okay, your task is basically multi-part: 1 - Traversing a sheet with smart logic 2 - Splitting 3 - Placing data in new cell

This isn't proofed for working but this should give you the ammunition to accomplish your goal with minor effort.

Sub mySub()
  dim i,j,dummy,dummy2, blankCounter
  i=1
  j=1
  blankCounter=0
  '@@@@@@Section1
  do while ThisWorkbook.Worksheets(1).Cells(1, j)<>""
  do while blankCounter<=5
        if ThisWorkbook.Worksheets(1).Cells(i, j)=""
          blankCounter=blankCounter+1
        else
        '@@@@@@Section2
       dummy=Split(ThisWorkbook.Worksheets(1).Cells(i, j),"—")
       for k=0 to Ubound(dummy)
        ThisWorkbook.Worksheets(2).Cells(i, j+k)=dummy(k)
       next k
        end if
    i=i+1
  loop
                    blankCounter=0
        j=j+1
  loop


end Sub

okay, in english. Section one is looking at this workbook ("this" appearing commonly especially in java but this is a more palpable example of such logic). In this workbook look at my first worksheet. In this worksheet lets look at col1,row1. Now we are going to loop down the rows and keep going until they are blank, do the same with the columns. This will basically find the dimensions of your matrix (data).

Okay, so now that we can traverse the workbook we are half way there.

Now you get to know the Split functionality in VBA that also exists in almost every language. Split looks for something in your String and breaks up your string by the argument you supply. Dummy will hold whatever the return values are. The size of dummy is unknown, so we should not hardcode this otherwise it will break when the splitter is not present.

That being said, we now introduce Ubound. Ubound is a functionality that finds the size of your array. Note on this: if Ubound returns 1, this means you have one value. Arrays are auto indexed to 0 so you must note this and not loop from 1 to Ubound, this will give an error and also produce incorrect code.

Okay, good now that we can traverse our array and our workbook now we just need to put these values in the cells desired. var k should be able to do this for you.

As i said i haven't proofed this code, so you are going to need to make sure that this logic functions properly, as i realize now that it does not wrt placing the data in the correct cell, but this will be yours to solve.