1

I have been trying to piece together a functional workbook, making use of VBA code as I go.

I currently have a macro created that looks at a dynamic range of values in a column, and converts them to a new value. 

Sub ConvertWireSize()
Dim i As Long
Sheets("Circuits").Select
Range("H1").Select
For i = 1 To Rows.Count
    If Cells(i, 8).Value = 0.5 Then
        Cells(i, 8).Value = 20
    ElseIf Cells(i, 8).Value = 0.8 Then
        Cells(i, 8).Value = 18
    ElseIf Cells(i, 8).Value = 1 Then
        Cells(i, 8).Value = 16
    ElseIf Cells(i, 8).Value = 2 Then
        Cells(i, 8).Value = 14
    ElseIf Cells(i, 8).Value = 3 Then
        Cells(i, 8).Value = 12
    ElseIf Cells(i, 8).Value = 5 Then
        Cells(i, 8).Value = 10
    ElseIf Cells(i, 8).Value = 8 Then
        Cells(i, 8).Value = 8
    ElseIf Cells(i, 8).Value = 13 Then
        Cells(i, 8).Value = 6
    ElseIf Cells(i, 8).Value = 19 Then
        Cells(i, 8).Value = 4
      End If
Next i

MsgBox "Wire Size Has Been Converted From CSA to AWG."

Sheets("Main").Select

End Sub

This seems to be a very inefficient, and slow, way of doing things. I've been trying to piece together a new macro that would use VLookup, but the more research I do, the more confused I get.

Could someone help point me in the right direction?

Zac
  • 1,305
  • 3
  • 17
  • 28
John Shambles
  • 47
  • 5

1 Answers1

1

Do not go to row 1048576, only as far as the values in column H extend. Use a Select Case to clean up your IFs. Don't use Select.

Sub ConvertWireSize()
    Dim i As Long
    with workSheets("Circuits")
        For i = 1 To .cells(.Rows.Count, "H").end(xlup).row
            select case .Cells(i, 8).Value2
                case  0.5
                    .Cells(i, 8).Value = 20
                case  0.8
                    .Cells(i, 8).Value = 18
                case  1
                    .Cells(i, 8).Value = 16
                ...
             end select
        Next i
    end with

    MsgBox "Wire Size Has Been Converted From CSA to AWG."

    workSheets("Main").Select   
End Sub

I couldn't readily determine a linear pattern to the conversion but you might be able to tweak some maths to do each conversion.

If you have more than a few thousand of these to convert then an in-memory array would show an appreciable increase in efficiency.