Macro - Loop Question

[mojo_123]

Good Evening,

First off, I'd like to thank everyone for answering VB related questions. With your help I was able to familiarize myself somewhat with VB.
Since this is my first post, I'd like to apologize if this question has already been asked. I searched on Google as well as this forum's own search engine, but failed to come up with a solution.

I'm in a situation that requires filtering. But since i was unable to successfully execute a macro that selects the first cell and pastes formula, I tried another method.

What I'm trying to do is basically create a condition when met copies a cell (with Formulas or just the formula) and replaces two other cells in the same row.

So far this is what I came up with:

Dim rng As Range
Sheets("DAILY").Select
Range("X10").Select
Range(Range("X10"), Range("X100000").End(xlDown)).Select

For Each rng In Selection


If ActiveCell.Text = "True" Then
ActiveCell.FormulaR1C1 = "=$P8/$L8"
ActiveCell.FormulaR1C1 = "=$M8*(1-VLOOKUP($C8,$Q$1:$R$6,2,0))"
ActiveCell.End(xlDown).Activate

End If

Next rng


But i keep getting errors trying to execute the macro.

Can anyone help me or provide me with advise as to what would be an efficient way?

Thanks,
Mojo

 

[JoeMo]

You might improve your chances of getting help if you provide some more information about what you want to accomplish and details about the layout of data on the sheet. Your code is placing a formula in the active cell if its value is "True" and then immediately overwriting that formula with a different formula - probably not what you intended.

 

[mojo_123]

Thanks for the quick reply. You're right, thats not exactly what i intended.

So here's a summary of what I'm looking at.

	A	B	C	D	E	fegp%	G	H	I	J	K	L	M
1
2				#DIV/0!	#N/A		FALSE
3	Qty	Sales	Fegp	unit	cost	fegp%	season	Field2	Dept	Field3
4	4	$116.00	#N/A	#N/A	#N/A	#N/A					4	#N/A	FALSE
5	1	$6.94	($0.13)	7.07	7.07	-0.01873					1	7.07	FALSE
6	40	$960.00	#N/A	#N/A	#N/A	#N/A					40	#N/A	FALSE
7	-2	($78.00)	($43.07)	17.46286	-34.9257	0.552234					-2	-34.9257	FALSE
8
	- If column M4=True then copy (cell E4 and F4 or "=$Q2/$M2" or "=$N2*(1-VLOOKUP($D4,$R$1:$S$7,2,0))" formula to column E4 and F4
	- If False, move on to the next cell (ie, N5)

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

i hope that helps.

 

[JoeMo]

Try this on a copy of your sheet (untested) - not clear to me if the formula in column E is meant to be absolute or relative.

Sub mojo_123()
Dim lR As Long, c As Range
lR = Range("M" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("M4", "M" & lR)
    If c.Value = True Then
        c.Offset(, -8).Formula = "=$Q2/$M2"
        c.Offset(, -7).Formula = "=$N2*(1-VLOOKUP($D" & c.Row & ",$R$1:$S$7,2,0))"
    End If
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[mojo_123]

Hi Joe,

I modified the formula a bit to fit these cells:

6	N	P	P	Q	R	S	T	U	V	W	X
7
8		#N/A	#N/A		FALSE
9	Fegp	cost/unit	cost	fegp%	season	Field2	Dept	Field3
10	#N/A	#N/A	#N/A	#N/A					4	#N/A	#N/A
11	($0.13)	7.07	7.07	-0.01873					1	7.07	FALSE

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

Dim lR As Long, c As Range
lR = Range("X" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("X10", "X" & lR)
If c.Value.Text = True Then
c.Offset(, -9).Formula = "=$p2/$L2"
c.Offset(, -8).Formula = "=$M2*(1-VLOOKUP($C" & c.Row & ",$P$1:$R$6,2,0))"
End If
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

But i keep getting stuck at " If c.Value.Text = True Then " --> Identifier under cursor is not recognized.

In regards to the formula in column E. Its supposed to be relative. So would it be efficient for me to copy and paste the formula or place it (like we have now).

Thanks again for your help.

 

[mojo_123]

How could i change my range for If c.Value = True Then to work?

 

[JoeMo]

Change this:
If c.Value.Text = True Then
to this:
If c.Value = True Then

If you want the formula in E to be relative to the row then use this:
c.Offset(, -9).Formula = "=$p" & c.Row & "/$L" & c.Row

 

[mojo_123]

It still keeps giving the same error message:

Run-Time Error '13':
Type Mismatch


Dim lR As Long, c As Range
lR = Range("X" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("X10", "X" & lR)
If c.Value = True Then
c.Offset(, -9).Formula = "=$p2/$L2"
c.Offset(, -8).Formula = "=$M2*(1-VLOOKUP($C" & c.Row & ",$P$1:$R$6,2,0))"
End If
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[JoeMo]

I can't reproduce that error. There's nothing I can see that would cause a type mismatch error at that line - it runs fine for me. Check that line for typos or retype it.