PDA

View Full Version : Solved: Project Help! Type Mistmatch ~ Stumped!



mikeoly
12-07-2012, 11:48 PM
Hey all, I'm new the the forum! I'm working on a project for Monday and cannot get past this point. I'm trying to add all the ranges together to put into another specific range.


Public Sub cbOK_Click()
Dim Risk1 As Variant
Dim Risk2 As Variant
Dim Risk3 As Variant
Dim Risk4 As Variant
Dim Risk5 As Variant
Dim RiskCompile As Variant
If cbRisk1.Value = True Then
Set Risk1 = ActiveWorkbook.Worksheets("Sheet3").Range("R1." &
ComboBox1.Value)
ActiveWorkbook.Worksheets("Sheet1").Range("D18:H25").Value =
Risk1
End If
If cbRisk2.Value = True Then
Set Risk2 = ActiveWorkbook.Worksheets("Sheet3").Range("R2." &
ComboBox2.Value)
End If
If cbRisk3.Value = True Then
Set Risk3 = ActiveWorkbook.Worksheets("Sheet3").Range("R3." &
ComboBox3.Value)
End If
If cbRisk4.Value = True Then
Set Risk4 = ActiveWorkbook.Worksheets("Sheet3").Range("R4." &
ComboBox4.Value)
End If
If cbRisk5.Value = True Then
Set Risk5 = ActiveWorkbook.Worksheets("Sheet3").Range("R5." &
ComboBox5.Value)
End If

RiskCompile = ActiveWorkbook.Worksheets("Sheet8").Range("D18:H25")
RiskCompile = Risk1 + Risk2 + Risk3

```````````````````````````````````````````````````````````
*Bolded is where I get my type mismatch. I put a watch on those variables and they all seem to be the same type. If I solely set RiskCompile = Risk1 it works...as soon as I try to add something, things go south.. :banghead:

I greatly appreciate any help in advance! :thumb

Simon Lloyd
12-08-2012, 01:19 AM
Look at your ranges.Range("R1." & ....the range format is R1:" & ... you used a dot rather than a full colon!

mikeoly
12-08-2012, 11:04 AM
Look at your ranges.Range("R1." & ....the range format is R1:" & ... you used a dot rather than a full colon!
Sorry! It is actually the name of the range, for example, R1.1, R1.2 etc etc

Apologies for not making that clear.

mikeoly
12-08-2012, 02:12 PM
The ranges are simply a 5x8 space that I need to add together and insert into another 5x8 space in another sheet. Any good help links on adding ranges? Can't find anything that's worked for me :/

Paul_Hossler
12-08-2012, 03:06 PM
Test 1 -- Most streight forward ( = brute force) is to just add the cells

Test 2 -- Application.Evaluate works, but you have to build the string


Option Explicit
Sub test()
Dim r As Long, c As Long
With Range("RiskCompile")

If cbRisk1.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Range("Risk1").Cells(r, c).Value
Next c
Next r
End If

If cbRisk2.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Range("Risk2").Cells(r, c).Value
Next c
Next r
End If


End With
End Sub

Sub test2()
Dim s As String
s = "="
s = s & Range("R1.1").Address & "+"
s = s & Range("R1.2").Address & "+"
s = s & Range("R1.3").Address

' Range("R.Compile").FormulaArray = s

Range("R.Compile").Value = Application.Evaluate(s)

End Sub


Paul

mikeoly
12-08-2012, 03:43 PM
Paul, I really appreciate you posting and helping me out! Truly. I've put that into my code and now am troubleshooting a 'Method' range of object global failed error, any ideas?

So if I choose cbRisk1 & cbRisk2 to be true, then the error would come up here:



Public Sub cbOK_Click()


Dim Risk1 As Variant


Dim Risk2 As Variant


Dim Risk3 As Variant


Dim Risk4 As Variant


Dim Risk5 As Variant


Dim r As Long, c As Long














If cbRisk1.Value = True Then


Set Risk1 = ActiveWorkbook.Worksheets("Sheet3").Range("R1." &
ComboBox1.Value)





End If





If cbRisk2.Value = True Then


Set Risk2 = ActiveWorkbook.Worksheets("Sheet3").Range("R2." &
ComboBox2.Value)





End If





If cbRisk3.Value = True Then


Set Risk3 = ActiveWorkbook.Worksheets("Sheet3").Range("R3." &
ComboBox3.Value)





End If





If cbRisk4.Value = True Then


Set Risk4 = ActiveWorkbook.Worksheets("Sheet3").Range("R4." &
ComboBox4.Value)





End If





If cbRisk5.Value = True Then


Set Risk5 = ActiveWorkbook.Worksheets("Sheet3").Range("R5." &
ComboBox5.Value)





End If











With Range("RiskCompile")





If cbRisk1.Value = True Then


For r = 1 To .Rows.Count


For c = 1 To .Columns.Count


.Cells(r, c).Value = .Cells(r, c).Value +
Range("Risk1").Cells(r, c).Value


Next c


Next r


End If





If cbRisk2.Value = True Then


For r = 1 To .Rows.Count


For c = 1 To .Columns.Count


.Cells(r, c).Value = .Cells(r, c).Value + Range("Risk2").Cells(r,
c).Value


Next c


Next r


End If





If cbRisk3.Value = True Then


For r = 1 To .Rows.Count


For c = 1 To .Columns.Count


.Cells(r, c).Value = .Cells(r, c).Value +
Range("Risk3").Cells(r, c).Value


Next c


Next r


End If





If cbRisk4.Value = True Then


For r = 1 To .Rows.Count


For c = 1 To .Columns.Count


.Cells(r, c).Value = .Cells(r, c).Value +
Range("Risk4").Cells(r, c).Value


Next c


Next r


End If





If cbRisk5.Value = True Then


For r = 1 To .Rows.Count


For c = 1 To .Columns.Count


.Cells(r, c).Value = .Cells(r, c).Value +
Range("Risk5").Cells(r, c).Value


Next c


Next r


End If











End With


Unload Me


End Sub


Best,
Mike

Teeroy
12-08-2012, 04:16 PM
You are using an inconsistent range reference format. The format "Range("Risk2").Cells(r, c).Value" refers to a named range (Risk2) on a worksheet but you are setting Range objects. Try instead "Risk2.Cells(r, c).Value"

mikeoly
12-08-2012, 05:13 PM
You are using an inconsistent range reference format. The format "Range("Risk2").Cells(r, c).Value" refers to a named range (Risk2) on a worksheet but you are setting Range objects. Try instead "Risk2.Cells(r, c).Value"
Thanks a bunch! Works!!