Applying code to entire column

[RCG_80]

Hi, first attempt at using vba, but i've compiled an IF statement (below), this works fine, but how do i apply this to the rest of the column, the dataset i'm working on has potential to be 15000 lines of data, any help is appreciated

[VBA]Sub Format()
Dim Config As String
Config = ("AA4")

If Range("K4").Value = "CD" Or Range("K4").Value = "CD+" Then
Range(Config).Value = "1"
ElseIf Range("K4").Value = "CD+DVD more CD" Or Range("K4").Value = "SINGLE" Then
Range(Config).Value = "1"
ElseIf Range("K4").Value = "LP" Then
Range(Config).Value = "3"
ElseIf Range("K4").Value = "DVD" Then
Range(Config).Value = "60"
ElseIf Range("K4").Value = "DVD+CD more DVD" Then
Range(Config).Value = "69"
ElseIf Range("K4").Value = "Universal Media Disc" Then
Range(Config).Value = "90"

End If[/VBA]

[BrianMH]

Assuming there are no empty rows between the top and bottom and assuming it starts at row 4 this will work.

[VBA]Sub Format()

Dim c As Range

Set c = Range("K4")

While c.Value <> ""

If c.Value = "CD" Or c.Value = "CD+" Then
Range("AA" & c.Row).Value = "1"
ElseIf c.Value = "CD+DVD more CD" Or c.Value = "SINGLE" Then
Range("AA" & c.Row).Value = "1"
ElseIf c.Value = "LP" Then
Range("AA" & c.Row).Value = "3"
ElseIf c.Value = "DVD" Then
Range("AA" & c.Row).Value = "60"
ElseIf c.Value = "DVD+CD more DVD" Then
Range("AA" & c.Row).Value = "69"
ElseIf c.Value = "Universal Media Disc" Then
Range("AA" & c.Row).Value = "90"
End If
Set c = c.Offset(1, 0)
Wend


End Sub
[/VBA]

[Bob Phillips]

[VBA]Sub Macro1()
Dim Config As Range
Dim lastrow As Long
Dim i As Long

Set Config = Range("AA1")

With ActiveSheet

lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For i = 4 To lastrow

Select Case .Cells(i, "K").Value

Case "CD", "CD+": Config.Cells(i, 1).Value = "1"
Case "CD+DVD more CD", "SINGLE": Config.Cells(i, 1).Value = "1"
Case "LP": Config.Cells(i, 1).Value = "3"
Case "DVD": Config.Cells(i, 1).Value = "60"
Case "DVD+CD more DVD": Config.Cells(i, 1).Value = "69"
Case "Universal Media Disc": Config.Cells(i, 1).Value = "90"
End Select
Next i
End With
End Sub[/VBA]

[RCG_80]

Thanks guys really helpful, it works great. However, another issue if anyone can help, i have another set of coding which i've tried varying ways to expand to the whole column but it just won't work or my pc crashes, any ideas how to apply the following to entire column, or suggestions how to make run more smoothly, this is not all my code just an example as this section runs on over many variables,

Sub subConfig()
Dim subConfig As String
subConfig = "AB4"

If Range("K4").Value = "CD" And Range("L4").Value = "1" Then
Range(subConfig).Value = "5"
ElseIf Range("K4").Value = "CD" And Range("L4").Value = "2" Then
Range(subConfig).Value = "D"
ElseIf Range("K4").Value = "CD" And Range("L4").Value = "3" Or Range("L4").Value = "4" Then
Range(subConfig).Value = "H"
ElseIf Range("K4").Value = "CD" And Range("L4").Value = "5" Then
Range(subConfig).Value = "G"
ElseIf Range("K4").Value = "CD" And Range("L4").Value = "6" Or Range("L4") = "7" Or Range("L4") = "8" Then
Range(subConfig).Value = "R"
ElseIf Range("K4").Value = "CD" And Range("L4").Value = "9" Or Range("L4") = "10" Or Range("L4") = "11" Or Range("L4") = "12" Then
Range(subConfig).Value = "T"

Any help would be massively appreciated

[BrianMH]

Please can you be sure to tag code with the VBA tags as it makes it easier to read. We are happy to help you learn. Can you please post an example of what you have tried to make it work? Perhaps your code is close and just needs a tweak.

[Bob Phillips]

Isn't that we have just given yoou?