Codes to long Possible Array may help ?

**realniceguy5000** · 03-26-2009, 02:34 PM

Hi Everyone,

I have a script here and wanted some advice on how to make it easier to write. I need to transer the values in cells C77:N77
C88:N88 to cells C26:N26

and
C102:N102
C113:N113 to cells C54:N54

Based on when a value is picked in my combo box.
It will only move data from one row to another based on what is picked.

Here is what I have so far. Which works it just a lot of writing to do I thought maybe someone had a better Idea maybe an aaray to pick the cells?

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

Thank You, Mike

**Kenneth Hobson** · 03-26-2009, 03:52 PM

Range("C54").Value = Range("C102:N102").Value

Rather than all those Ifs, try: Select Case

**realniceguy5000** · 03-26-2009, 04:18 PM

Thank you,

but not sure how that will work. But I will try...

When someone picks Jan 09 for example the script should take the values from cells c77 to n77 and place that value in c26 to n26 and also take the values from cells c102 to n102 and place in c54 to n54

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

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

Hope that is a better explanation.

Thanks for any help, Mike

**realniceguy5000** · 03-27-2009, 07:22 AM

Cross posting this thread to see if someone else can help me out with this problem.

http://www.mrexcel.com/forum/showthread.php?t=380518

Thank You,

**Shijesh Kumar** · 03-27-2009, 07:49 AM

Its just a part of code to give you a direction...

dim source_row as integer

dim target_row as integer
dim col as integer

source_row =77
target_row = 26
col = 3


tempValue = ComboBox1.value
if tempValue ="Jan 09" then
   range( source_row , col) = range( target_row , col)            
   col = col+1
end if

**realniceguy5000** · 03-27-2009, 08:19 AM

Originally Posted by Shijesh Kumar

Its just a part of code to give you a direction...

dim source_row as integer

dim target_row as integer
dim col as integer

source_row =77
target_row = 26
col = 3


tempValue = ComboBox1.value
if tempValue ="Jan 09" then
   range( source_row , col) = range( target_row , col)               col = col+1
end if

Hi and Thank you

I'm getting an error with this part of the code in red when I pick "Jan 09" in the combo box. The error is method Range of object_worksheet failed?

Thank You, Mike