+ Reply to Thread
Results 1 to 5 of 5

VB code almost works...but can't find a minor mistake

  1. 03-16-2006, 12:00 PM #1
    NadiaR via OfficeKB.com
    Guest

    VB code almost works...but can't find a minor mistake

    I have something that almost works...but I can't get it to copy the entire
    Column AD, where am i making a mistake.
    Thanks in advance,
    Nadia

    'Take ComboBox choice
    Dim MyMonth As String
    MyMonth = ComboBox1.Value

    'loop Calc columns
    Dim Calc_Col As Integer
    Calc_Col = 1
    Do While Worksheets("Calculation").Cells(5, Calc_Col).Value <> MyMonth
    Calc_Col = Calc_Col + 1
    Loop

    'loop Calc rows
    Dim Calc_Row As Integer
    Calc_Row = 5
    Do While Trim(Worksheets("Calculation").Cells(Calc_Row, Calc_Col).Value) <>
    ""
    Calc_Row = Calc_Row + 1
    Loop
    Calc_Row = Calc_Row - 1

    'loop Data Columns
    Dim Data_Col As Integer
    Data_Col = 1
    Do While Worksheets("Data").Cells(5, Data_Col).Value <> MyMonth
    Data_Col = Data_Col + 1
    Loop

    'translate Calc_Col
    Dim Calc_ColL As String
    Select Case Calc_Col
    Case 30: Calc_ColL = "AD"
    Case 31: Calc_ColL = "AE"
    Case 32: Calc_ColL = "AF"
    Case 33: Calc_ColL = "AG"
    Case 34: Calc_ColL = "AH"
    Case 35: Calc_ColL = "AI"
    Case 36: Calc_ColL = "AJ"
    Case 37: Calc_ColL = "AK"
    Case 38: Calc_ColL = "AL"
    Case 39: Calc_ColL = "AM"
    Case 40: Calc_ColL = "AN"
    Case 41: Calc_ColL = "AO"
    Case 42: Calc_ColL = "AP"
    Case 43: Calc_ColL = "AQ"
    Case 44: Calc_ColL = "AR"
    Case 45: Calc_ColL = "AS"
    Case 46: Calc_ColL = "AT"
    End Select

    'Copy & Paste value
    Dim MyRange As String
    MyRange = Calc_ColL & "6:" & Calc_ColL & Calc_Row
    'Worksheets("Calculation").Range(Cells(6, Calc_Col), Cells(Calc_Row, Calc_Col)
    ).Select
    Worksheets("Calculation").Select
    Worksheets("Calculation").Range(MyRange).Select
    Selection.Copy
    Worksheets("Data").Select
    Worksheets("Data").Cells(6, Data_Col).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    End Sub

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200603/1
    Reply With QuoteRegister To Reply
  2. 03-16-2006, 12:10 PM #2
    NadiaR via OfficeKB.com
    Guest

    Re: VB code almost works...but can't find a minor mistake

    I need to copy and paste special an entire column regardless of whether or
    not it has 0 in the column.

    NadiaR wrote:
    >I have something that almost works...but I can't get it to copy the entire
    >Column AD, where am i making a mistake.
    >Thanks in advance,
    >Nadia
    >
    >'Take ComboBox choice
    >Dim MyMonth As String
    >MyMonth = ComboBox1.Value
    >
    >'loop Calc columns
    >Dim Calc_Col As Integer
    >Calc_Col = 1
    >Do While Worksheets("Calculation").Cells(5, Calc_Col).Value <> MyMonth
    > Calc_Col = Calc_Col + 1
    >Loop
    >
    >'loop Calc rows
    >Dim Calc_Row As Integer
    >Calc_Row = 5
    >Do While Trim(Worksheets("Calculation").Cells(Calc_Row, Calc_Col).Value) <>
    >""
    > Calc_Row = Calc_Row + 1
    >Loop
    >Calc_Row = Calc_Row - 1
    >
    >'loop Data Columns
    >Dim Data_Col As Integer
    >Data_Col = 1
    >Do While Worksheets("Data").Cells(5, Data_Col).Value <> MyMonth
    > Data_Col = Data_Col + 1
    >Loop
    >
    >'translate Calc_Col
    >Dim Calc_ColL As String
    >Select Case Calc_Col
    > Case 30: Calc_ColL = "AD"
    > Case 31: Calc_ColL = "AE"
    > Case 32: Calc_ColL = "AF"
    > Case 33: Calc_ColL = "AG"
    > Case 34: Calc_ColL = "AH"
    > Case 35: Calc_ColL = "AI"
    > Case 36: Calc_ColL = "AJ"
    > Case 37: Calc_ColL = "AK"
    > Case 38: Calc_ColL = "AL"
    > Case 39: Calc_ColL = "AM"
    > Case 40: Calc_ColL = "AN"
    > Case 41: Calc_ColL = "AO"
    > Case 42: Calc_ColL = "AP"
    > Case 43: Calc_ColL = "AQ"
    > Case 44: Calc_ColL = "AR"
    > Case 45: Calc_ColL = "AS"
    > Case 46: Calc_ColL = "AT"
    >End Select
    >
    >'Copy & Paste value
    >Dim MyRange As String
    >MyRange = Calc_ColL & "6:" & Calc_ColL & Calc_Row
    >'Worksheets("Calculation").Range(Cells(6, Calc_Col), Cells(Calc_Row, Calc_Col)
    >).Select
    >Worksheets("Calculation").Select
    >Worksheets("Calculation").Range(MyRange).Select
    >Selection.Copy
    >Worksheets("Data").Select
    >Worksheets("Data").Cells(6, Data_Col).Select
    >Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    > :=False, Transpose:=False
    >
    >End Sub

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200603/1
    Reply With QuoteRegister To Reply
  3. 03-16-2006, 12:20 PM #3
    Tom Ogilvy
    Guest

    RE: VB code almost works...but can't find a minor mistake

    change
    Worksheets("Calculation").Range(MyRange).Select
    to
    Worksheets("Calculation").Range(MyRange).Entirecolumn.Select

    and
    Worksheets("Data").cells(6, Data_Col).Select

    to
    Worksheets("Data").Columns(Data_Col).Select

    Of course you can then probably eliminate a lot of other code, but that is
    your decision.

    --
    Regards,
    Tom Ogilvy




    "NadiaR via OfficeKB.com" wrote:

    > I have something that almost works...but I can't get it to copy the entire
    > Column AD, where am i making a mistake.
    > Thanks in advance,
    > Nadia
    >
    > 'Take ComboBox choice
    > Dim MyMonth As String
    > MyMonth = ComboBox1.Value
    >
    > 'loop Calc columns
    > Dim Calc_Col As Integer
    > Calc_Col = 1
    > Do While Worksheets("Calculation").Cells(5, Calc_Col).Value <> MyMonth
    > Calc_Col = Calc_Col + 1
    > Loop
    >
    > 'loop Calc rows
    > Dim Calc_Row As Integer
    > Calc_Row = 5
    > Do While Trim(Worksheets("Calculation").Cells(Calc_Row, Calc_Col).Value) <>
    > ""
    > Calc_Row = Calc_Row + 1
    > Loop
    > Calc_Row = Calc_Row - 1
    >
    > 'loop Data Columns
    > Dim Data_Col As Integer
    > Data_Col = 1
    > Do While Worksheets("Data").Cells(5, Data_Col).Value <> MyMonth
    > Data_Col = Data_Col + 1
    > Loop
    >
    > 'translate Calc_Col
    > Dim Calc_ColL As String
    > Select Case Calc_Col
    > Case 30: Calc_ColL = "AD"
    > Case 31: Calc_ColL = "AE"
    > Case 32: Calc_ColL = "AF"
    > Case 33: Calc_ColL = "AG"
    > Case 34: Calc_ColL = "AH"
    > Case 35: Calc_ColL = "AI"
    > Case 36: Calc_ColL = "AJ"
    > Case 37: Calc_ColL = "AK"
    > Case 38: Calc_ColL = "AL"
    > Case 39: Calc_ColL = "AM"
    > Case 40: Calc_ColL = "AN"
    > Case 41: Calc_ColL = "AO"
    > Case 42: Calc_ColL = "AP"
    > Case 43: Calc_ColL = "AQ"
    > Case 44: Calc_ColL = "AR"
    > Case 45: Calc_ColL = "AS"
    > Case 46: Calc_ColL = "AT"
    > End Select
    >
    > 'Copy & Paste value
    > Dim MyRange As String
    > MyRange = Calc_ColL & "6:" & Calc_ColL & Calc_Row
    > 'Worksheets("Calculation").Range(Cells(6, Calc_Col), Cells(Calc_Row, Calc_Col)
    > ).Select
    > Worksheets("Calculation").Select
    > Worksheets("Calculation").Range(MyRange).Select
    > Selection.Copy
    > Worksheets("Data").Select
    > Worksheets("Data").Cells(6, Data_Col).Select
    > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    > :=False, Transpose:=False
    >
    >
    > End Sub
    >
    > --
    > Message posted via OfficeKB.com
    > http://www.officekb.com/Uwe/Forums.a...mming/200603/1
    >
    Reply With QuoteRegister To Reply
  4. 03-16-2006, 01:35 PM #4
    NadiaR via OfficeKB.com
    Guest

    Re: VB code almost works...but can't find a minor mistake

    I found my problem...for anyone wanting to copy and pastespecial just a
    column on a specified date based on a combo box...for each month, without
    overwriting the previous month...here is the solution!

    Sub Cp_Mon()
    '
    'Take ComboBox choice
    Dim MyMonth As String
    MyMonth = ComboBox1.Value

    'loop Calc columns
    Dim Calc_Col As Integer
    Calc_Col = 1
    Do While Worksheets("Calculation").Cells(5, Calc_Col).Value <> MyMonth
    Calc_Col = Calc_Col + 1
    Loop

    'loop Calc rows
    Dim Calc_Row As Integer
    Calc_Row = 5
    Do While Trim(Worksheets("Calculation").Cells(Calc_Row, Calc_Col).Value) <>
    "donotdeletedr"
    Calc_Row = Calc_Row + 1
    Loop
    Calc_Row = Calc_Row - 1

    'loop Data Columns
    Dim Data_Col As Integer
    Data_Col = 1
    Do While Worksheets("Data").Cells(5, Data_Col).Value <> MyMonth
    Data_Col = Data_Col + 1
    Loop

    'translate Calc_Col
    Dim Calc_ColL As String
    Select Case Calc_Col
    Case 30: Calc_ColL = "AD"
    Case 31: Calc_ColL = "AE"
    Case 32: Calc_ColL = "AF"
    Case 33: Calc_ColL = "AG"
    Case 34: Calc_ColL = "AH"
    Case 35: Calc_ColL = "AI"
    Case 36: Calc_ColL = "AJ"
    Case 37: Calc_ColL = "AK"
    Case 38: Calc_ColL = "AL"
    Case 39: Calc_ColL = "AM"
    Case 40: Calc_ColL = "AN"
    Case 41: Calc_ColL = "AO"
    Case 42: Calc_ColL = "AP"
    Case 43: Calc_ColL = "AQ"
    Case 44: Calc_ColL = "AR"
    Case 45: Calc_ColL = "AS"
    Case 46: Calc_ColL = "AT"
    End Select

    'Copy & Paste value
    Dim MyRange As String
    MyRange = Calc_ColL & "6:" & Calc_ColL & Calc_Row
    'Worksheets("Calculation").Range(Cells(6, Calc_Col), Cells(Calc_Row, Calc_Col)
    ).Select
    Worksheets("Calculation").Select
    Worksheets("Calculation").Range(MyRange).Select
    Selection.Copy
    Worksheets("Data").Select
    Worksheets("Data").Cells(6, Data_Col).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    End Sub


    NadiaR wrote:
    >I need to copy and paste special an entire column regardless of whether or
    >not it has 0 in the column.
    >
    >>I have something that almost works...but I can't get it to copy the entire
    >>Column AD, where am i making a mistake.
    >[quoted text clipped - 64 lines]
    >>
    >>End Sub

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200603/1
    Reply With QuoteRegister To Reply
  5. 03-16-2006, 01:35 PM #5
    NadiaR via OfficeKB.com
    Guest

    RE: VB code almost works...but can't find a minor mistake

    Thanks Tom

    Tom Ogilvy wrote:
    >change
    >Worksheets("Calculation").Range(MyRange).Select
    >to
    >Worksheets("Calculation").Range(MyRange).Entirecolumn.Select
    >
    >and
    >Worksheets("Data").cells(6, Data_Col).Select
    >
    >to
    >Worksheets("Data").Columns(Data_Col).Select
    >
    >Of course you can then probably eliminate a lot of other code, but that is
    >your decision.
    >
    >> I have something that almost works...but I can't get it to copy the entire
    >> Column AD, where am i making a mistake.
    >[quoted text clipped - 64 lines]
    >>
    >> End Sub

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200603/1
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1