+ Reply to Thread
Results 1 to 3 of 3

Applying code to entire column

Hybrid View

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    sydney, australia
    MS-Off Ver
    excel 2007/2010
    Posts
    1

    Applying code to entire column

    Hi Guys,

    I'm pretty new to vba, and i've written some code which works fine apart from i need it to run down an entire column, as it stands it's just one cell.

    Here's a section of the code, it is considerably longer but it's pretty much repeating the same format just looking for different data.
    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"
    I've tried a few a couple of ways to apply it to the column but it just seems to crash or error. Any help is hugely appreciated
    Last edited by arlu1201; 05-17-2012 at 02:54 AM. Reason: Please put code tags in future.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Applying code to entire column

    Maybe

    Sub subConfiguration()
    Dim subConfig As String
    subConfig = "AB4"
    
        Select Case Range("K4") & Range("L4")
            Case "CD1"
                Range(subConfig) = 5
            Case "CD2"
                Range(subConfig) = "D"
            Case "CD3", "CD4"
                Range(subConfig) = "H"
            Case "CD5"
                Range(subConfig) = "G"
            Case "CD6", "CD7", "CD8"
                Range(subConfig) = "R"
            Case "CD9", "CD10", "CD11", "CD12"
                Range(subConfig) = "T"
            Case Else
                Range(subConfig) = ""
        End Select
    
    End Sub

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,442

    Re: Applying code to entire column

    You should read the forum rules and add code tags to your code extract.

    However, just to make you welcome ...

    Untested code:

    Sub subConfig()
    Dim lLR As Long
    Dim i As Long
    Dim subConfig As String
    lLR = Range("K" & Rows.Count).End(xlUp).Row
    subConfig = "AB"
    
    For i = 4 To lLR
        If Range("K" & i).Value = "CD" And Range("L" & i).Value = "1" Then
            Range(subConfig & i).Value = "5"
        ElseIf Range("K" & i).Value = "CD" And Range("L" & i).Value = "2" Then
            Range(subConfig & i).Value = "D"
        ElseIf Range("K" & i).Value = "CD" And Range("L" & i).Value = "3" Or Range("L" & i).Value = "4" Then
            Range(subConfig & i).Value = "H"
        ElseIf Range("K" & i).Value = "CD" And Range("L" & i).Value = "5" Then
            Range(subConfig & i).Value = "G"
        ElseIf Range("K" & i).Value = "CD" And Range("L" & i).Value = "6" Or Range("L" & i) = "7" Or Range("L" & i) = "8" Then
            Range(subConfig & i).Value = "R"
        ElseIf Range("K" & i).Value = "CD" And Range("L" & i).Value = "9" Or Range("L" & i) = "10" Or Range("L" & i) = "11" Or Range("L" & i) = "12" Then
            Range(subConfig & i).Value = "T"
        End If
    Next 'i
    End Sub
    Last edited by arlu1201; 05-17-2012 at 02:54 AM. Reason: Moved to right forum.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1