• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help using VBA to insert a formula

T

Tome9499

New Member
I need to edit the VBA code below to insert the following formula into my sheet:

[pre]
Code: 
Formula
=IF(COUNTIF($A148:$R148,"Unsatisfied")>0,"X",IF(COUNTIF($A148:$R148,"Very Unsatisfied")>0,"X",""))
[/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.
 
Whoa, that's a lot of repeating code! Might want to think about using some loops there, like have your variable be rngeQ(1 to 16)

Anyways, for your question, you need to concatenate in the variable, something like this:

[pre]
Code: 
rngeNegative(j, 1) = "=IF(COUNTIF($A" & j & ":$R" & j & ",""Unsatisfied"")>0,""X"",IF(COUNTIF($A" & _
j & ":$R" & j & ",""Very Unsatisfied"")>0,""X"",""""))"
[/pre]
 
Luke,


It worked . . . mostly. I had to modify the code because all of the results were offset by one cell.


Code: 
rngeNegative(j, 1) = "=IF(COUNTIF($A" & j + 1 & ":$R" & j + 1 & ",""Unsatisfied"")>0,""X"",IF(COUNTIF($A" & j + 1 & ":$R" & j + 1 & ",""Very Unsatisfied"")>0,""X"",""""))"


I agree that there is alot of code in this block. Unfortunately, I'm new to VBA, and am working with pre-existing code. I don't know how to shrink the code reliably. I'm open to suggesions if you have any.


Thank you for your help,


TomE
 
OK, I got the code below to work. Good suggestion:

[pre]
Code: 
Dim j As Integer
Dim rngeAge As Variant
Dim rngeFormula As Variant
Dim rngeSessionID() As Variant
Dim rngeSubmit() As Variant
Dim rngeQ(1 To 16) As Variant
Dim rngeNegative() As Variant
[/pre]
 
Glad the line is working.

Possible suggestions:

Rather than have the code recalculate the UBound everytime, do it once at the beginning and then use the shorter variable

[pre]
Code: 
xSize = UBound(rngeAge,1)
'and then later
ReDim rngeQ1(1 to xSize)
Note that you don't need to specify an array size's width if you're just having it be 1.


Actually, you have the rngeAge hardcoded to be a specific range, so you already know what the UBound number will be. Will this change, or could you just go ahead and use the value of 14999?


Looking closer at your code, you take A2:V15000, load it into an array (rngeAge), then seperate it out into 18 components, of which only 1 seems to have an additional operation performed on it (the rngeSubmit). Then it all gets put back into the workbook...

Looking at what actually gets done in the macro, maybe this would work?

Sub ShortenedCode()
Dim xCount As Integer
'This becomes the key driver, determines how many rows to look at
xCount = 15000

Application.ScreenUpdating = False

'Check and modify the cells of concern
For i = 2 To 15000
If Cells(i, 2) <> "" Then
Cells(i, 2).Value = Cells(i, 2).Value + 0.125
End If
Next i

'Put the formula all in one go, using RC referencing:
Range(Cells(2, "Z"), Cells(xCount, "Z")).FormulaR1C1 = _
"=IF(COUNTIF(R[1]C1:R[1]C18,""Unsatisfied"")>0,""X"",IF(COUNTIF(R[1]C1:R[1]C18,""Very Unsatisfied"")>0,""X"",""""))"
Application.ScreenUpdating = True

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top