I need to edit the VBA code below to insert the following formula into my sheet:
[pre]
[/pre]
I am trying to add the formula as the value for the variable "rngeNegative" in the code below (which copies a block of data from Sheet1, does a few transforms, and then pastes the data into Sheet4).
Dim j As Integer
Dim rngeAge As Variant
Dim rngeFormula As Variant
Dim rngeSessionID() As Variant
Dim rngeSubmit() As Variant
Dim rngeQ1() As Variant
Dim rngeQ2() As Variant
Dim rngeQ3() As Variant
Dim rngeQ4() As Variant
Dim rngeQ5() As Variant
Dim rngeQ6() As Variant
Dim rngeQ7() As Variant
Dim rngeQ8() As Variant
Dim rngeQ9() As Variant
Dim rngeQ10() As Variant
Dim rngeQ11() As Variant
Dim rngeQ12() As Variant
Dim rngeQ13() As Variant
Dim rngeQ14() As Variant
Dim rngeQ15() As Variant
Dim rngeQ16() As Variant
Dim rngeNegative() As Variant
rngeAge = Sheet1.Range("A2:V15000"
.Value
ReDim rngeSessionID(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeSubmit(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ1(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ2(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ3(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ4(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ5(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ6(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ7(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ8(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ9(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ10(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ11(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ12(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ13(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ14(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ15(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ16(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeNegative(1 To UBound(rngeAge, 1), 1 To 1)
For j = 1 To UBound(rngeAge, 1)
On Error GoTo enditall
If rngeAge(j, 2) <> "" Then
rngeSessionID(j, 1) = rngeAge(j, 1)
rngeSubmit(j, 1) = rngeAge(j, 2) + 0.125
rngeQ1(j, 1) = rngeAge(j, 3)
rngeQ2(j, 1) = rngeAge(j, 4)
rngeQ3(j, 1) = rngeAge(j, 5)
rngeQ4(j, 1) = rngeAge(j, 6)
rngeQ5(j, 1) = rngeAge(j, 7)
rngeQ6(j, 1) = rngeAge(j, 8)
rngeQ7(j, 1) = rngeAge(j, 9)
rngeQ8(j, 1) = rngeAge(j, 10)
rngeQ9(j, 1) = rngeAge(j, 11)
rngeQ10(j, 1) = rngeAge(j, 12)
rngeQ11(j, 1) = rngeAge(j, 13)
rngeQ12(j, 1) = rngeAge(j, 14)
rngeQ13(j, 1) = rngeAge(j, 15)
rngeQ14(j, 1) = rngeAge(j, 16)
rngeQ15(j, 1) = rngeAge(j, 17)
rngeQ16(j, 1) = rngeAge(j, 18)
rngeNegative(j, 1) = rngeAge(j, 19)
End If
Next j
Sheet4.Range("A2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeSessionID
Sheet4.Range("B2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeSubmit
Sheet4.Range("C2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ1
Sheet4.Range("D2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ2
Sheet4.Range("E2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ3
Sheet4.Range("F2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ4
Sheet4.Range("G2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ5
Sheet4.Range("H2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ6
Sheet4.Range("I2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ7
Sheet4.Range("J2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ8
Sheet4.Range("K2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ9
Sheet4.Range("L2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ10
Sheet4.Range("M2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ11
Sheet4.Range("N2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ12
Sheet4.Range("O2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ13
Sheet4.Range("P2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ14
Sheet4.Range("Q2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ15
Sheet4.Range("R2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeQ16
Sheet4.Range("Z2"
.Resize(UBound(rngeAge, 1), 1).Value = rngeNegative
My biggest hurdle is that I do not know how to convert the row references to accomodate the variable integer "j"
Thanks in advance for any help you can offer.
[pre]
Code:
Formula
=IF(COUNTIF($A148:$R148,"Unsatisfied")>0,"X",IF(COUNTIF($A148:$R148,"Very Unsatisfied")>0,"X",""))
I am trying to add the formula as the value for the variable "rngeNegative" in the code below (which copies a block of data from Sheet1, does a few transforms, and then pastes the data into Sheet4).
Dim j As Integer
Dim rngeAge As Variant
Dim rngeFormula As Variant
Dim rngeSessionID() As Variant
Dim rngeSubmit() As Variant
Dim rngeQ1() As Variant
Dim rngeQ2() As Variant
Dim rngeQ3() As Variant
Dim rngeQ4() As Variant
Dim rngeQ5() As Variant
Dim rngeQ6() As Variant
Dim rngeQ7() As Variant
Dim rngeQ8() As Variant
Dim rngeQ9() As Variant
Dim rngeQ10() As Variant
Dim rngeQ11() As Variant
Dim rngeQ12() As Variant
Dim rngeQ13() As Variant
Dim rngeQ14() As Variant
Dim rngeQ15() As Variant
Dim rngeQ16() As Variant
Dim rngeNegative() As Variant
rngeAge = Sheet1.Range("A2:V15000"
ReDim rngeSessionID(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeSubmit(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ1(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ2(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ3(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ4(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ5(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ6(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ7(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ8(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ9(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ10(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ11(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ12(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ13(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ14(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ15(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeQ16(1 To UBound(rngeAge, 1), 1 To 1)
ReDim rngeNegative(1 To UBound(rngeAge, 1), 1 To 1)
For j = 1 To UBound(rngeAge, 1)
On Error GoTo enditall
If rngeAge(j, 2) <> "" Then
rngeSessionID(j, 1) = rngeAge(j, 1)
rngeSubmit(j, 1) = rngeAge(j, 2) + 0.125
rngeQ1(j, 1) = rngeAge(j, 3)
rngeQ2(j, 1) = rngeAge(j, 4)
rngeQ3(j, 1) = rngeAge(j, 5)
rngeQ4(j, 1) = rngeAge(j, 6)
rngeQ5(j, 1) = rngeAge(j, 7)
rngeQ6(j, 1) = rngeAge(j, 8)
rngeQ7(j, 1) = rngeAge(j, 9)
rngeQ8(j, 1) = rngeAge(j, 10)
rngeQ9(j, 1) = rngeAge(j, 11)
rngeQ10(j, 1) = rngeAge(j, 12)
rngeQ11(j, 1) = rngeAge(j, 13)
rngeQ12(j, 1) = rngeAge(j, 14)
rngeQ13(j, 1) = rngeAge(j, 15)
rngeQ14(j, 1) = rngeAge(j, 16)
rngeQ15(j, 1) = rngeAge(j, 17)
rngeQ16(j, 1) = rngeAge(j, 18)
rngeNegative(j, 1) = rngeAge(j, 19)
End If
Next j
Sheet4.Range("A2"
Sheet4.Range("B2"
Sheet4.Range("C2"
Sheet4.Range("D2"
Sheet4.Range("E2"
Sheet4.Range("F2"
Sheet4.Range("G2"
Sheet4.Range("H2"
Sheet4.Range("I2"
Sheet4.Range("J2"
Sheet4.Range("K2"
Sheet4.Range("L2"
Sheet4.Range("M2"
Sheet4.Range("N2"
Sheet4.Range("O2"
Sheet4.Range("P2"
Sheet4.Range("Q2"
Sheet4.Range("R2"
Sheet4.Range("Z2"
My biggest hurdle is that I do not know how to convert the row references to accomodate the variable integer "j"
Thanks in advance for any help you can offer.