Create Specific Text down Column Based on Specific Text in Another Column

[AlwaysLearning2018]

Hi All,

I have created the below macro that will populate a value in 1 specific cell based on 1 specific cell in a different column. My question is how do I look at the entire column for that word and populate the other word down a different column Any help would be much appreciated!


Sub Comments()


If Worksheets(1).Range("D54").Value = "Variation Margin" Then
Worksheets(1).Range("I75").Value = "Futures Margin"

End If
End Sub

 

[ravishankar]

try these codes

Dim a As Integer
a = WorksheetFunction.Match("variation margin", Worksheet("sheet1").Range("D:D"), 0)
If a > 0 Then
Worksheet("sheet1").Range("I25") = "It exists in D" & a
End If

 

[AlwaysLearning2018]

Hi,

I get an error "sub or function not defined"

 

[Fluff]

Couple of typos in the code try

Dim a As Integer
a = WorksheetFunction.Match("variation margin", Worksheet[COLOR=#ff0000]s[/COLOR]("sheet1").Range("D:D"), 0)
If a > 0 Then
Worksheet[COLOR=#ff0000]s[/COLOR]("sheet1").Range("I25") = "It exists in D" & a
End If

 

[AlwaysLearning2018]

Hi,

Thank you beru much for your reply. Still get an error message when I add the "s" as noted.

 

[Fluff]

In that case, please post you entire code showing which line is causing the problem & what is the error message?

 

[AlwaysLearning2018]

Hi Fluff,

Example of what I am attempting to do below.

If Range("C33").Value = "Variation Margin" Then
Range("F33").Value = "Futures Margin"

I'm not sure how to change this to get the code to read all instances of the specific word in Column C to return a certain word in Column F. The above works when I refer to 1 specific cell but if I say "C2:C800" For example I get a type mismatch. Excel Criteria below. Thanks!!

TD	VD	TRANSACTION	UNITS	Portfolio	Client Memo
10/30/2018	11/2/2018	Maturity	1	200
10/30/2018	11/2/2018	Maturity	2	400
10/30/2018	11/2/2018	Maturity	3	401
10/30/2018	11/2/2018	Maturity	4	402
10/30/2018	11/2/2018	Maturity	5	403
10/30/2018	11/2/2018	Maturity	6	404
10/30/2018	11/2/2018	Maturity	7	405
10/30/2018	11/2/2018	Repo	8	406
10/30/2018	11/2/2018	Repo	9	407
10/30/2018	11/2/2018	Repo	10	408
10/30/2018	11/2/2018	Repo	11	409
10/30/2018	11/2/2018	Repo	12	410
10/30/2018	11/2/2018	Repo	13	411
10/30/2018	11/2/2018	Repo	14	412
10/30/2018	11/2/2018	Repo	15	413
10/30/2018	11/2/2018	Purchase	16	414
10/30/2018	11/2/2018	Purchase	17	415
10/30/2018	11/2/2018	Purchase	18	416
10/30/2018	11/2/2018	Purchase	19	417
10/30/2018	11/2/2018	Purchase	20	418
10/30/2018	11/2/2018	Purchase	21	419
10/30/2018	11/2/2018	Purchase	22	420
10/30/2018	11/2/2018	Purchase	23	900
10/30/2018	11/2/2018	Purchase	24	901
10/30/2018	11/2/2018	Maturity	25	902
10/30/2018	11/2/2018	Maturity	26	903
10/30/2018	11/2/2018	Swap	27	904
10/30/2018	11/2/2018	Swap	28	905
10/30/2018	11/2/2018	Swap	29	906
10/30/2018	11/2/2018	Purchase	30	907
10/30/2018	11/2/2018	Maturity	31	908
10/30/2018	11/2/2018	Variation Margin	32	275	Futures Margin
10/30/2018	11/2/2018	Variation Margin	33	280
10/30/2018	11/2/2018	Variation Margin	34	818
10/30/2018	11/2/2018	Variation Margin	35	500
10/30/2018	11/2/2018	Variation Margin	36	335
10/30/2018	11/2/2018	Variation Margin	37	158
10/30/2018	11/2/2018	Variation Margin	38	157
10/30/2018	11/2/2018	Variation Margin	39	165
10/30/2018	11/2/2018	Variation Margin	40	917
10/30/2018	11/2/2018	Swap Income	41	918
10/30/2018	11/2/2018	Swap Income	42	919
10/30/2018	11/2/2018	Variation Margin	43	920
10/30/2018	11/2/2018	Variation Margin	44	921
10/30/2018	11/2/2018	Variation Margin	45	922
10/30/2018	11/2/2018	Variation Margin	46	923
10/30/2018	11/2/2018	Variation Margin	47	924
10/30/2018	11/2/2018	Purchase	48	925
10/30/2018	11/2/2018	Sale	49	926
10/30/2018	11/2/2018	sale	50	927
10/30/2018	11/2/2018	sale	51	928
10/30/2018	11/2/2018	sale	52	929
10/30/2018	11/2/2018	Purchase	53	930

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="64" style="width: 48pt;" span="2"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <tbody> </tbody>

 

[Fluff]

Try

Sub AlwaysLearning2018()
   With Range("F2", Range("C" & Rows.Count).End(xlUp).Offset(, 3))
      .Value = Evaluate(Replace("If(@=""Variation Margin"",""Futures Margin"","""")", "@", .Offset(, -3).Address))
   End With
End Sub

 

[AlwaysLearning2018]

Hi,

Thank you. I get a run time error 1004: Application defined or object defined error when attempting this code.

 

[Fluff]

What line gives that error?