Codes to long Possible Array may help ?

**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

**Shijesh Kumar** · 03-27-2009, 08:23 AM

Can u post a sample workbook with dummy data so that i can have a look at that..

I have written this code for sheet module... may be thats the reason for error..

Try changing Range to activesheet.range(..

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

Here is the workbook. However you will need to run the script below the one we are working with to populate the combo box for some reason the data keeps going away.

Thanks for your help, Mike

**Shijesh Kumar** · 03-27-2009, 08:53 AM

try this one

Private Sub ComboBox1_Change()



Dim r As Integer
Dim c1 As Integer
Dim c2 As Integer

r = 77 + ComboBox1.ListIndex

If r <> 76 Then
c1 = 3
c2 = 14

Set source_range = Range(Cells(r, c1), Cells(r, c2))
Set destination_range = Range(Cells(26, c1), Cells(26, c2))

source_range.Copy

destination_range.PasteSpecial xlValues
End If

End Sub

**realniceguy5000** · 03-27-2009, 09:11 AM

Working Now... Thank You for your input. I'm still amazed out how good you guys are!!!

Here is the code I ended up with to get what I needed.

Once again that you for your help!!!! Mike

Private Sub ComboBox1_Change()
Dim r As Integer
Dim R2 As Integer

Dim c1 As Integer
Dim c2 As Integer

r = 77 + ComboBox1.ListIndex
R2 = 102 + ComboBox1.ListIndex

If r <> 76 Then
c1 = 3
c2 = 14

Set source_range = Range(Cells(r, c1), Cells(r, c2))
Set destination_range = Range(Cells(26, c1), Cells(26, c2))
source_range.Copy
destination_range.PasteSpecial xlValues
Set source_range = Range(Cells(R2, c1), Cells(R2, c2))
Set destination_range = Range(Cells(54, c1), Cells(54, c2))
source_range.Copy
destination_range.PasteSpecial xlValues

End If

End Sub