Script to long anyway to make it shorter?

realniceguy5000

realniceguy5000

Board Regular
Joined
Aug 19, 2008
Messages
148
Hi,

Note had this post on another web site but I didn't get any anwsers that helped.So I thought I would try here.

http://www.excelforum.com/excel-pro...long-possible-array-may-help.html#post2066266

I have this script below that will be very long by the time I am done entering all the cell info. I wanted to know if there may be a better way or shorter way to get the same results.

Here is what needs to happen:
When someone picks Jan 09 from my combo box for example the script should take the values from row cells c77 to n77 and place that value into row c26 to n26 and also take the values from row cells c102 to n102 and place in row c54 to n54

However if someone were to pick Feb 09 from my combo box the data should move down one row so the example would look like row c78 to n78 data displays in row c26 to n26 and row c103 to n103 data displays in row c54 to n54

So that data will always display in row c26 to n26 and c54 to n54 only where the data is pulled from should change by the value in the combo box?

Thanks for any help, Mike

Here is the code I have so far, I just think there may be a better way of to get this done? Someone suggested maybe using "CASE" and I way thinking maybe an aaray selection for the rows?

Code: 
Private Sub ComboBox1_Change()
Dim tempValue As String
   
   tempValue = ComboBox1.Value
'Range("C54:N54").Value = Range("C102:N102")'THIS WONT WORK
   If tempValue = "Jan 09" Then
   Range("C26").Value = Range("C77")
   Range("D26").Value = Range("D77")
   Range("E26").Value = Range("E77")
   
   
ElseIf tempValue = "Feb 09" Then
ElseIf tempValue = "Mar 09" Then
ElseIf tempValue = "Apr 09" Then
ElseIf tempValue = "May 09" Then
ElseIf tempValue = "Jun 09" Then
ElseIf tempValue = "Jul 09" Then
ElseIf tempValue = "Aug 09" Then
ElseIf tempValue = "Sep 09" Then
ElseIf tempValue = "Oct 09" Then
ElseIf tempValue = "Nov 09" Then
ElseIf tempValue = "Dec 09" Then
   
   Else
      
        
   End If
End Sub

Thanks Again...<!-- / message -->
 
Sort by date Sort by votes
Hello,

is this working as expected?

Code: 
Private Sub ComboBox1_Change()
Dim tempValue As String
   
   
   tempValue = ComboBox1.Value
'Range("C54:N54").Value = Range("C102:N102")'THIS WONT WORK
    Select Case tempValue
        Case "Jan 09"
            MY_OFFSET = 1
        Case "Feb 09"
            MY_OFFSET = 2
        'cary on CASE here
    End Select
    Range("C76:E76").Offset(MY_OFFSET, 0).Copy Range("C26")
    Range("C101:N101").Offset(MY_OFFSET, 0).Copy Range("C54")
End Sub
 
Upvote 0
Something like:
Code: 
Private Sub ComboBox1_Change()
Dim tempValue As String, lngOffset as long
If Combobox.Value <> "" Then
   tempValue = "01 " & ComboBox1.Value
   lngOffset = Month(tempvalue) - 1
   Range("C26:N26").Value = Range("C77:N77").Offset(lngOffset).Value   
Range("C54:N54").Value = Range("C102:N102").Offset(lngOffset).Value
End If
End Sub
 
Upvote 0
Hello and thanks for the help. I have a couple problems with both scripts...

The first one
Rich (BB code): 
Private Sub ComboBox1_Change()
Dim tempValue As String
 
 
   tempValue = ComboBox1.Value
    Select Case tempValue
        Case "Jan 09"
            MY_OFFSET = 1
        Case "Feb 09"
            MY_OFFSET = 2
        'cary on CASE here
    End Select
    Range("C76:E76").Offset(MY_OFFSET, 0).Copy Range("C26")
    Range("C101:N101").Offset(MY_OFFSET, 0).Copy Range("C54")
End Sub

The data that is put into the cells all has "#REF" error?
Also it is only putting data into c,d,e 26 it is missing f26-n26
however it is populating c54-n54

The second example
Rich (BB code): 
Private Sub ComboBox1_Change()
Dim tempValue As String, lngOffset as long
If Combobox.Value <> "" Then
   tempValue = "01 " & ComboBox1.Value
   lngOffset = Month(tempvalue) - 1
   Range("C26:N26").Value = Range("C77:N77").Offset(lngOffset).Value   
Range("C54:N54").Value = Range("C102:N102").Offset(lngOffset).Value
End If
End Sub

I'm getting an object required error at the line in red?
not sure what to do here?

Thanks for all your help, Mike
 
Upvote 0
just change the line

Code: 
[/COLOR]
[COLOR=#ff0000]If Combobox.Value <> "" Then[/COLOR]
[COLOR=#ff0000]
to the one given below, you are missing the number 1 at the end of the name.

Code: 
[/COLOR]
[COLOR=#ff0000]If Combobox1.Value <> "" Then[/COLOR]
[COLOR=#ff0000]
 
Upvote 0
Yep, just change it to Combobox1 - apologies, it was air code! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
404
hagia_sofia
hagia_sofia
N
  • Solved
Vstack, Chooserows & Dollar
2
Replies
18
Views
2K
Nathan Asius
N
B
  • Question Question
VBA to edit/update cells within a large table from a common "Dashboard"
Replies
1
Views
625
brianv
B
N
  • Question Question
Code taking too long to end (5 minutes) how to speed up?
Replies
1
Views
621
johnnyL
J
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
2K
AndyEd
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top