Data Conversion Macro

**Houseofnerd** · 06-14-2011, 12:33 PM

Hello,

I'm new to VBA writing, so please pardon my ignorance.

I am writing a macro to convert a raw data sheet into a new clean data sheet. This entails taking a response and translating it into a number value so it can be statistically analyzed.

For example: taking a row of 'yes' or 'no' answers and placing them in the clean sheet in the same location as 1 or 2 respectively.

My question is how do I approach writing a code that will read a specific row and translate and insert the number value in the same row on another sheet?

Thank you

**davegugg** · 06-14-2011, 12:43 PM

You would use something like this:

With Sheets("Sheet1")
    If .Cells(i, j).Value = "Yes" Then
        Sheets("Sheet2").Cells(i, j).Value = 1
    Else
        Sheets("Sheet2").Cells(i, j).Value = 0
    End If
End With

Where i and j are the row and column numbers. You can use a loop to get through each column and row.
Also, if you have more than two options for what will be translated onto the second sheet, it may be better to use a Select Case like this:

With Sheets("Sheet1")
    Select Case .Cells(i, j).Value
        Case "Yes"
            Sheets("Sheet2").Cells(i, j).Value = 1
        Case "No"
            Sheets("Sheet2").Cells(i, j).Value = 0
        Case "Maybe"
            Sheets("Sheet2").Cells(i, j).Value = -1
        Case Else
            Sheets("Sheet2").Cells(i, j).Value = 10
        End Select
End With