vba simplify code

[badgerstrader]

ok so i have the following vba code


Sub AddFormula_TickDep()

Dim rng As Range
Dim ws1, ws2, ws3, ws4 As Worksheet
Dim result As String

Set ws1 = Sheets("Market")
Set ws2 = Sheets("Triggers")
Set ws3 = Sheets("Selection")
Set ws4 = Sheets("Odds")
Set rng = ws1.Range("AB11:AB50")

result = "=IFERROR(VLOOKUP(" & ws1.Range("A11").Address(0, 0) & "," & ws3.Range("$C$2:$D$28").Address(1, 1, , 1) & ",2,0),""0"")"

rng.Formula = result


End Sub

Currently cell AB11 to AB50 will show either the correct value or 0 however if i click on one of the cells i can see the formula
im looking to perform this in each sell from AB11 to AB50 without the Formula being visible if i click on one of the cells in question.

Thanks in advance.

 

[Waimea]

Hi, I think that you can hide formulas by protecting your worksheet.

Sub HideFormulas()
Worksheets("Sheet1").Range("A1:B1").FormulaHidden = True
End Sub

Read more at Range.FormulaHidden property (Excel)

 

[badgerstrader]

i dont want to protect the worksheet. i just dont want the formula to be shown.
basically im looking to copy the data from 1 cell next to another cell from worksheet 2 to worksheet 1 but only if that data is the same as the cell in a column on worksheet 1

so worksheet 1 cell a11:a30 may have the word help in one of the cells
if the same word is in worksheet 2 column c then i want it to copy the data that is in the cell next to it in column d to worksheet 1 cell AA for example

 

[Fluff]

Do you want to get rid of the formula completely?

 

[badgerstrader]

if i can transfer the data without the formula then yes.

 

[Fluff]

In that case just add this line as shown

rng.Formula = Result
rng.Value = rng.Value

End Sub

 

[badgerstrader]

Do you want to get rid of the formula completely?

if i can transfer the data without the formula then yes.

In that case just add this line as shown

rng.Formula = Result
rng.Value = rng.Value

End Sub

excellent thank you

 

[Fluff]

Glad we could help & thanks for the feedback.