I have a macro that looks up 14 cells of data and writes that data to 14
different worksheets in another spreadsheet.
I am using ivalue(xx) = .Range("J10") to retrieve the data in the cells
(with the xx being a 2 character variable to label the cell type, ie, AS, SG,
etc).
It works great until I get to the 11th variable. The 11th - 14th variables
return 0 as the value in the worksheet it is writing to. Will the iValue only
do up to 10 variables? If so, what can I do to get the addtional 4 I need?
Here is the code that gets and writes the iValue variable....it just repeats
14 times in the code. (Without the Dim line in the repeating code).

With wbData.Sheets(1)
iValueV = .Range("J12")
iValueAS = .Range("J11")
iValueSG = .Range("J10")
iValueCR = .Range("J13")
iValueCC = .Range("J14")
iValueCRate = .Range("J15")
iValueAVGS = .Range("J16")
iValueWP = .Range("J17")
iValueWPPS = .Range("J18")
OutRV = .Range("J19")
OutBSS = .Range("J20")
OutBMV = .Range("J21")
OutBIO = .Range("J22")
End With
' apply iValueV - Variance to matched row and column
With wbSum.Sheets(2)
Dim lastrow As Long, lastcol As Long, xV As Long, xR As Long, xC As Long
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
'get matching row
For xV = 1 To lastrow
If iOffice = .Cells(xV, 1) Then xR = xV
Next xV
If xR = 0 Then MsgBox "Office: " & iOffice & " not found in summary Table"
' get matching column
For xV = 1 To lastcol
If iDate = .Cells(1, xV) Then xC = xV
Next xV
If xC = 0 Then MsgBox "Date: " & iDate & " not found in summary table"
If xR 0 And xC 0 Then .Cells(xR, xC) = iValueV
End With

Opps, the code is as below here...not like I first posted it!

"David" wrote:

I have a macro that looks up 14 cells of data and writes that data to 14
different worksheets in another spreadsheet.
I am using ivalue(xx) = .Range("J10") to retrieve the data in the cells
(with the xx being a 2 character variable to label the cell type, ie, AS, SG,
etc).
It works great until I get to the 11th variable. The 11th - 14th variables
return 0 as the value in the worksheet it is writing to. Will the iValue only
do up to 10 variables? If so, what can I do to get the addtional 4 I need?
Here is the code that gets and writes the iValue variable....it just repeats
14 times in the code. (Without the Dim line in the repeating code).

With wbData.Sheets(1)
iValueV = .Range("J12")
iValueAS = .Range("J11")
iValueSG = .Range("J10")
iValueCR = .Range("J13")
iValueCC = .Range("J14")
iValueCRate = .Range("J15")
iValueAVGS = .Range("J16")
iValueWP = .Range("J17")
iValueWPPS = .Range("J18")
iValueRV = .Range("J19")
iValueBSS = .Range("J20")
iValueBMV = .Range("J21")
iValueBIO = .Range("J22")
End With
' apply iValueV - Variance to matched row and column
With wbSum.Sheets(2)
Dim lastrow As Long, lastcol As Long, xV As Long, xR As Long, xC As Long
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
'get matching row
For xV = 1 To lastrow
If iOffice = .Cells(xV, 1) Then xR = xV
Next xV
If xR = 0 Then MsgBox "Office: " & iOffice & " not found in summary Table"
' get matching column
For xV = 1 To lastcol
If iDate = .Cells(1, xV) Then xC = xV
Next xV
If xC = 0 Then MsgBox "Date: " & iDate & " not found in summary table"
If xR 0 And xC 0 Then .Cells(xR, xC) = iValueV
End With