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.

[VBA]
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
[/VBA]

**XLGibbs** · 02-14-2006, 04:00 PM

[VBA]
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
[/VBA]

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:
[VBA]
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
[/VBA]

**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:

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

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

[vba]
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
[/vba]

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

[vba]
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

[/vba]

**Shazam** · 02-15-2006, 07:55 PM

I put your code in but its giving me a error

[VBA] 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
[/VBA]

Is that correct?

**XLGibbs** · 02-15-2006, 08:05 PM

Originally Posted by Shazam

I put your code in but its giving me a error

[VBA] 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
[/VBA]

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

[vba]

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
[/vba]

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

[VBA]

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

[/VBA]

**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?

[VBA]
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[/VBA]

**XLGibbs** · 02-15-2006, 08:36 PM

Whoops. Nasty error on my part....

[vba]
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
[/vba]

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.

[VBA]

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[/VBA]

**XLGibbs** · 02-15-2006, 08:40 PM

Originally Posted by Shazam

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?

[VBA]
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[/VBA]

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

The below should cover all corrections made to this point. Disregard all earlier posts.
[vba]
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
[/vba]

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...

Thread: Solved: When find text insert formulas vba

Thread Tools

Display

Solved: When find text insert formulas vba

Posting Permissions