PDA

View Full Version : Solved: When find text insert formulas vba



Shazam
02-14-2006, 03:43 PM
Hey everyone!

The code below looks in column E for the word "Due" then it will insert the formula 4 columns over.

How can it be modified to look in row 2 for the word "Due" and insert the formula one column over below one row. Then it will filldown the formulas that coresponds to column B.

I put a sample workbook below. The worksheet tab results would give you a better explanation.


Sub Insert_Formula()

With ActiveSheet.Columns("E:E")
Set E = .Find("Due", LookIn:=xlValues, lookat:=xlWhole)
If Not E Is Nothing Then
firstAddress = E.Address
Do
E.Offset(0, 4).Formula = "=SUM(RC[-2]:RC[-1])"
Set E = .FindNext(E)
Loop While Not E Is Nothing And E.Address <> firstAddress
End If
End With
End Sub

XLGibbs
02-14-2006, 04:00 PM
Sub insertformula()
With Sheets("Results")
lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
For x = 5 To .Cells(2, .Columns.Count).End(xlToLeft).Column Step 3
.Range(.Cells(3, x + 1), .Cells(lastrow, x + 1)).FormulaR1C1 = "=Sum(RC[-2]:RC[-1])"
Next x


End With
End Sub


Based on your sample sheet, this would do what your results sheet indicates is necessary. It starts at column E, goes 3 columns over at a time, and puts the formula in every cell of the next column over, from 1 row down to the last row of "Customer"

If the structure is that way you can specify the last column to include rather than searching for the word "Due"

Pete

Shazam
02-14-2006, 04:19 PM
Thank You for replyying XLGibbs But can the code be modified to be worksheet friendly? ,like ActiveWorksheet because I get this workbook daily so the sheet tab names are really dates so it changes everyday. Also can it look for the word "Due" because its not always in the same coulmn.

XLGibbs
02-14-2006, 04:28 PM
Well that is quite a pain for you isn't it!

Yes, it can be modified:

Sub insertformula()
Dim x As Range, xRng As Range, rngFirst As Range
With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set xRng = .Range("2:2") 'always row two I hope...
Set x = xRng.Find("Due")
Set rngFirst = x
Do
Set x = xRng.FindNext(x)
Range(x.Offset(1, 1), .Cells(lastrow, x.Column + 1)).FormulaR1C1 = "=Sum(RC[-2]:RC[-1])"
Loop Until x.Address = rngFirst.Address
End With
End Sub

Shazam
02-14-2006, 04:32 PM
Thnaks XLGibbs it works well. May I say it for you.


SHAZAM!!!

XLGibbs
02-14-2006, 04:35 PM
No problem. Just mark the thread solved if there is nothing else on this topic. Thanks Shazam.

Shazam
02-15-2006, 07:13 PM
Me again XLGibbs,


I been using your code with other workbooks and its fine But I have a couple of workbooks that has array formulas. I copy my formulas and put in your code but it does not work well. For a example:


.Range(.Cells(3, x + 1), .Cells(lastrow, x + 1)).FormulaArray = "=Sum(RC[-2]:RC[-1])"


Can we modified it?

XLGibbs
02-15-2006, 07:16 PM
That depends on the formula. the formula you have there is not an array formula, nor does it need to be....

I may need to see what you are referring to....the formula array syntax should work, but the formula must be correct to work...you can place the array formula in one cell and have the code autofill it down...

what is the actual formula as it appears in the sheet (obviously it will be missing the { } if you post it....

Shazam
02-15-2006, 07:30 PM
I have a few array formulas but I'm trying to input put this array formula:


{=SUM(SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":")*{-1,1,-1,1,-1,1})*24}

XLGibbs
02-15-2006, 07:39 PM
That seems like the hardest way I have seen to convert decimals to time after adding them up.....why is all that necessary? there may be a better way without having to deal with pasting an array formula like that...

XLGibbs
02-15-2006, 07:45 PM
Incidentally


Str = "=SUM(SUBSTITUTE(TEXT(r3c3:r3c8," & Chr(34) & "0.00" & Chr(34) & "," & Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

.Range(.Cells(3, x + 1), .Cells(lastrow, x + 1)).FormulaArray = Str


if you want it to self adjust for multiple rows like the other code then..



Str = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," & Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

.Range(.Cells(3, x + 1), .Cells(lastrow, x + 1)).FormulaArray = Str

Shazam
02-15-2006, 07:55 PM
I put your code in but its giving me a error


With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)
Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).Str = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," & Chr(34) & "." & Chr(34) & "," &
Chr (34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

.Range(.Cells(3, x + 1), .Cells(lastRow, x + 1)).FormulaArray = str
End With




Is that correct?

XLGibbs
02-15-2006, 08:05 PM
I put your code in but its giving me a error


With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)
Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).Str = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," & Chr(34) & "." & Chr(34) & "," &
Chr (34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

.Range(.Cells(3, x + 1), .Cells(lastRow, x + 1)).FormulaArray = str
End With




Is that correct?

No, it is not the way I posted it either..you need to keep the _ from line to line as I did to designated a continuing line
my code said make the variable "str" equal the formula then put the formularray = str



With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)

Str = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & ", _
& Chr(34) & "." & Chr(34) & "," & _
Chr (34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).FormulaArray =str
'Edit: added this assuming you need it ..

Loop Until y.address = rngFirst.address

End With


I am also unsure why there is a Do in that snippet, as there is no With in the snippet indicating which object this applies to, nor is there a Loop Until statement to say when to stop. Obviously, certain elements of proper syntax are assumed to be known by you to apply this...

I edited the syntax to be more correct based on my last solution above which you are trying to apply
Hope that helps...let me know..

Shazam
02-15-2006, 08:14 PM
Its giving me a error. Its says:
Compile Error:
Argument not Optional

Its highlighting this:

str




Sub insertformula()
Dim y As Range, yRng As Range, rngFirst As Range
With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)

str = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).FormulaArray = str
End With
End Sub

XLGibbs
02-15-2006, 08:20 PM
Shazam...my bad, poor choice of variable..

Change str to strFormula in both places...it produces the correct formula on my end..

Sorry bout that

Shazam
02-15-2006, 08:34 PM
I did made the changes and now its giving me another error. It says:

Compile Error:
End With Without With

So I took it out and it gives me this:

Compile Error:
Do Without Loop


What do you think?






Sub insertformula()
Dim y As Range, yRng As Range, rngFirst As Range
With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)

strFormula = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).FormulaArray = strFormula

End Sub

XLGibbs
02-15-2006, 08:36 PM
Whoops. Nasty error on my part....


strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column + 0)).FormulaArray = strformula


big time bad syntax on the r1c1 format...assuming you need to keep the columns C to H consisent for each row...

Shazam
02-15-2006, 08:40 PM
It still giving me the same error.






Sub insertformula()
Dim y As Range, yRng As Range, rngFirst As Range
With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)

strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.Column + 0)).FormulaArray = strformula

End With

End Sub

XLGibbs
02-15-2006, 08:40 PM
I did made the changes and now its giving me another error. It says:

Compile Error:
End With Without With

So I took it out and it gives me this:

Compile Error:
Do Without Loop


What do you think?






Sub insertformula()
Dim y As Range, yRng As Range, rngFirst As Range
With ActiveSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y
Do
Set y = yRng.FindNext(y)

strFormula = "=SUM(SUBSTITUTE(TEXT(rc3:rc8," & Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastRow, y.Column + 0)).FormulaArray = strFormula

End Sub


Well Shazam, it means you have a Do without Loop and With without End With...

You have to read my entire replies...I touched on that before, but I know this can get confusing..so no worries http://vbaexpress.com/forum/images/smilies/023.gif

The below should cover all corrections made to this point. Disregard all earlier posts.

With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
Set rngFirst = y

Strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column + 0)).FormulaArray = Strformula
Do
Set y = yRng.FindNext(y)

Strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," _
& Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column + 0)).FormulaArray = Strformula


Loop Until y.Address = rngFirst.Address

End With


let me know...

XLGibbs
02-15-2006, 08:44 PM
PS... it is a waste to be using VB simply to put the formula in....why not back up a bit and see if we can't do the math right in the code.......

most of the methods you are employing to find a header and the apply a formula down the column next to it, 1 row down, strikes me as really inefficient. Although I am happy to help you achieve whatever result you want...just wondering if we can;t help you apply a more proficient solution to the grand design...

Shazam
02-15-2006, 08:45 PM
It give me this error:

Run-Time error '91':

Object variable or with block variable
not set



Also can the code be relative because the word "totals" changes to column to column?

XLGibbs
02-15-2006, 08:51 PM
Yes, it means it did not find a match.

Make it relative? Relative to what? How about we get it working, then talk about how to make it relative.

At some point, being able to learn from the logic being applied has to kick in....try stepping through the code to see what is happening (use the F8 key)

I would be happy to explain the logic if need be....

With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
If Not y Is Nothing Then
Set rngFirst = y

Strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column + 0)).FormulaArray = Strformula
Do
Set y = yRng.FindNext(y)

Strformula = "=SUM(SUBSTITUTE(TEXT(C" & y.Offset(1, 0) & ":H" & y.Offset(1, 0) & "," _
& Chr(34) & "0.00" & Chr(34) & "," _
& Chr(34) & "." & Chr(34) & "," _
& Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column + 0)).FormulaArray = Strformula


Loop Until y.Address = rngFirst.Address
Else: MsgBox "No match found"
Exit Sub
End If
End With

XLGibbs
02-15-2006, 08:58 PM
Okay the word "Totals" changes column to column. That certainly would have been a useful nugget of information awhile ago since the code's logic is looking for that word across row 2.

Is it safe to assume that you still want it go 1 column over from 3 to the lastrow down?

What words does it need to search for?

Shazam
02-15-2006, 09:09 PM
Thnak You XLGibbs Its excellent. Thank You very much. What I meant was that the formula to be in r1c1 in the code like the other code you provided. becasue the word total is not going to be in the same column. I get the workbook by email from the customer and the codes you provided will help greatly. So the formula I provide like this:

{=SUM(SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":")*{-1,1,-1,1,-1,1})*24}

Could be this the next day:

{=SUM(SUBSTITUTE(TEXT(L3:Q3,"0.00"),".",":")*{-1,1,-1,1,-1,1})*24}

If the word "totals" is in column K

Should the code stil work?

I'll test it more tomorrow at work.

Once again thank You.

XLGibbs
02-15-2006, 09:13 PM
No, the code won;t adjust that well, however.....

how are the columns identified relative to the word total...specifically, how many columns to the left or right of the word Total is the leftmost part of the range....

it is always 6 columns wide, so if I can know what position to start that part of the formula..I can make the formula work as you need it.

Also, does it need to find the word total more than once? If not, the Do ...Loop can be eliminated...as the first find would take the formula, and if Total only occurs once then it could just stop there instead of looping...

Shazam
02-15-2006, 09:18 PM
No, the code won;t adjust that well, however.....

how are the columns identified relative to the word total...specifically, how many columns to the left or right of the word Total is the leftmost part of the range....

it is always 6 columns wide, so if I can know what position to start that part of the formula..I can make the formula work as you need it.

Also, does it need to find the word total more than once? If not, the Do ...Loop can be eliminated...as the first find would take the formula, and if Total only occurs once then it could just stop there instead of looping...


The word "totals" will only occure once at the right side of the worksheet.

XLGibbs
02-15-2006, 09:27 PM
Okay. This will find the word "total" ONCE in row 2.

This formula will set the range to be from one column to the right to 6 columns to the right..and relative to each row it is in.

So if "Total" is in column B2, then Range is C3:H3
if total is in column K2 Range would be L3:Q3 for row 3, L4:Q4 for row 4

based on the below code:


With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
If Not y Is Nothing Then
Set rngFirst = y

strFormula = "=SUM(SUBSTITUTE(TEXT(RC[1]:RC[6]," & Chr(34) & "0.00" & _
Chr(34) & "," & Chr(34) & "." & Chr(34) & "," & _
Chr(34) & ":" & Chr(34) & ")*{-1,1,-1,1,-1,1})*24"

Range(y.Offset(1, 0), .Cells(lastrow, y.column)).FormulaArray = strFormula

Else: MsgBox "No match found"
Exit Sub
End If
End With



Hope this helps.

Note: LastRow variable is defined by the last row in COLUMN B above....change as needed to be sure you get the right column to base the last row on (1 is A, 2 is B, 3 is C etc) make sure that the column number in that lastrow statement is the one needed...
Pete

Shazam
02-15-2006, 09:29 PM
I'm letting you know I'm go to sleep. I'll check back tomorrow.

Here is couple arrays formulas I'm currently using. That I'm going to apply to your code.

=SUM(IF(B3:B8=I2,IF(ISNUMBER(C3:H8),(C3:H8>0)+0)))

=AVERAGE(IF(ISNUMBER(C3:AG3),(C3:AG3)))


Thanks XLGibbs

XLGibbs
02-15-2006, 09:55 PM
I'm letting you know I'm go to sleep. I'll check back tomorrow.

Here is couple arrays formulas I'm currently using. That I'm going to apply to your code.

=SUM(IF(B3:B8=I2,IF(ISNUMBER(C3:H8),(C3:H8>0)+0)))

=AVERAGE(IF(ISNUMBER(C3:AG3),(C3:AG3)))

Thanks XLGibbs


Your welcome Shazam. But yikes. Converting formulas like that to VB is just poor planning...are you sure you want to go this route?


=SUM(IF(B3:B8=I2,IF(ISNUMBER(C3:H8),(C3:H8>0)+0)))
Logic: base is B2 as before so b3:b8 is the offset(1,0) range...but down 5 rows from there, same column. I2 is 1 row up, 7 columns over from there.
C3:H8 is same row, but 5 rows down and 6 columns right for whole range to look at.


strFormula = "=SUM(IF(RC:R[5]C=R[-1]C[7],IF(ISNUMBER(RC[1]:R[5]C[6]),( RC[1]:R[5]C[6] >0)+0)))"


=AVERAGE(IF(ISNUMBER(C3:AG3),(C3:AG3)))

Logic: Assuming that base is column B2 for these formulas using the existing scenarios...then C3:AG3 is 1 column right to 31 columns right of column B and 1 row down (your range(cells(y.offset(1,0)) takes care of the row)


strFormula = "=AVERAGE(IF(ISNUMBER(RC[1]:RC[31]),(RC[1]:RC[31])))"


PS. YOu can see the R1C1 syntax in the excel sheet by switching your sheet to R1C1 format....

Nasty ugly stuff.

Shazam
02-16-2006, 06:08 AM
I tried your code on one of my workbooks and its not filling down to adjust. I attach the workbook I'm currently using. Look at worksheet forcast in cell AZ3. Then look at worksheet results it should give you a better explanation.

XLGibbs
02-16-2006, 06:35 AM
Shazam, you need to be careful trying to blanket the syntax accross mutliple formats. If you aren't clear on the necessary adjustments to both the formula and the code, then it will make problems for you when trying to apply the logic accross all kinds of scenarios....


The array formula needs to be entered row by row in this case, otherwise ti won't won't be row specific like the non array formula was...

You also had the the formula referring to 1 row above for the ISNUMBER() check...

this works on the results sheet, but not on the other sheet (since Total is in row 1 on the other sheet...


Sub InsertFormula()


With ActiveSheet
lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row

Set yRng = .Range("2:2") 'always row two I hope...
Set y = yRng.Find("totals")
If Not y Is Nothing Then
Set rngFirst = y

For x = 1 To lastrow - 2
strFormula = "=SUMPRODUCT(IF(ISNUMBER(RC[-48]:RC[-1]),IF(RC[-48]:RC[-1]>0,RC[-48]:RC[-1])))"

y.Offset(x, 0).FormulaArray = strFormula
Next x
Else: MsgBox "No match found"
Exit Sub
End If
End With
End Sub

Shazam
02-16-2006, 06:49 AM
Thank You so much for all this great help. I think could get the other formulas to work.

Thank You!:bow: