New to macros, looking for logic assistance

**eastond** · 06-23-2008, 09:17 AM

I have been given a task to take data from a spreadsheet i was to create and re-format it so that it has a list of names, and then all skills these people what by there names and how much time they have spent working on these tasks. So that there are no empty cells, and it looks neat and ordered.

As it stands i have a spreadsheet with the max number of columns and 1647 rows, so i cant exactly do it without some help from a macro and my lack of experience with macros makes this alot harder for me.

I need to produce a macro that will help me with my task, and was wondering if anyone had some hints as to how i can go about making this macro? i tried recording one but i screwed it up and my VBA good enough to go through and alter the code to make it do as i need it to.

Any help / advice would be much apreciated.

Thanks.

**Richard Buttrey** · 06-23-2008, 09:26 AM

Hi

Can you upload an example of your workbook, leaving just a representative sample of rows rather than all 1647 if the file is large, and add some notes which explain what you're trying to achieve, which cells are involved, results you expect etc.

This will better assist us to advise.

Rgds

**eastond** · 06-23-2008, 09:44 AM

i can't at the moment as i am at work and any sort of uploading is disabled for security, but i can take them home and upload screen shots of them when i get home. Roughly what i have is:

_| Name | Name | Name |
S_|_
S_|_
S_|_
S_|_

Its basicly names across the top, skills down the side, and then values in the coresponding cells, but some of them cells are empty. And what my boss wants is:

_| Name | Skill | Value |
_|_
_|_
_|_
_|_

Dont know if that helps for the moment?

**Richard Buttrey** · 06-23-2008, 10:37 AM

Dont know if that helps for the moment?

Yes probably. Am I correct in thinking therefore that at the moment you have a large matrix where many of the cells at the intersection of a name and a skill have no values at all, whereas you want a straightforward list where names may be repeated if someone has more than one skill, and where you will have many more rows but only three columns?

Rgds

**eastond** · 06-23-2008, 10:52 AM

You got the first part right

What he wants is is something like:

Persons name | Skill | Time |
-----------------| Skill | Time |
-----------------| Skill | Time |
-----------------| Skill | Time |
Persons name | Skill | Time |
-----------------| Skill | Time |
-----------------| Skill | Time |
-----------------| Skill | Time |

And so on, from the massive spreadsheet i have.

thanks

**Richard Buttrey** · 06-23-2008, 02:17 PM

Hi,

Try the following. It assumes your data is on Sheet1 with the names starting in B1 and going across the columns and the skills in A2 going down the rows, and there is a Sheet2 which will receive the revised output.

Sub ListSkills()
Dim iNoNames As Integer, iskills As Integer, itimes As Integer, x As Integer, y As Integer, iVal As Integer
Sheet1.Activate
iskills = Application.WorksheetFunction.CountA(Range("A1:A65536"))
iNoNames = Range(Range("B1"), Range("B1").End(xlToRight)).Cells.Count

For x = 1 To iNoNames
    iVal = 0
    itimes = Application.WorksheetFunction.CountA(Range("B1:B65536").Offset(0, x - 1)) - 1
    
    If itimes > 0 Then
        Sheet2.Range("B65536").End(xlUp).Offset(1, -1) = Range("B1").Offset(0, x - 1)
        For y = 1 To iskills
            If Range("B1").Offset(y, x - 1) <> "" Then
                Sheet2.Range("A65536").End(xlUp).Offset(iVal, 1) = Sheet1.Range("A1").Offset(y, 0)
                Sheet2.Range("A65536").End(xlUp).Offset(iVal, 2) = Sheet1.Range("B1").Offset(y, x - 1)
                iVal = iVal + 1
            End If
        Next y
    End If

Next x

End Sub

HTH

**bendoly** · 06-24-2008, 10:00 AM

Fortunately macros are great for self-learning in VB. Starting a VB recording and taking several actions in the spreadsheet and then viewing the syntax provides a great start. UNFORTUNATELY there's much less you can learn this way in 2007 than in 2003 (recording many object manipulations isn't as simple in 2007). As for logic structure, the use of IF statements and Loops are probably where you want to start (aside from more basic data and storage logic).
Beyond the basics there's a much wider world out there for application developers - but it's hard to tap into even with a source like Excel Bible or a supposedly VB oriented text.
I would recommend a forthcoming text designed to ramp up skills from basic (or no) programming background into fairly advanced skills. Titled "Excel Basics to Blackbelt" by Cambridge. http://www.excelforum.com/index.php