Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VB code almost works...but can't find a minor mistake
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default VB code almost works...but can't find a minor mistake
change
Worksheets("Calculation").Range(MyRange).Select
to
Worksheets("Calculation").Range(MyRange).Entirecol umn.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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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).Entireco lumn.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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Bulk find and replace mistake ksh Excel Worksheet Functions 5 June 6th 08 06:37 AM
Code somewhat works. Please help? jsc3489 Excel Programming 4 December 2nd 05 07:51 PM
Code mistake Mirko Barkovic Excel Programming 4 September 29th 05 03:00 PM
Macro Code minor alteration please. RPTZ New Users to Excel 6 July 10th 05 05:47 AM
Why won't this code works ksnapp[_37_] Excel Programming 6 April 1st 04 01:44 PM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement