vba simplify code

badgerstrader

New Member
Joined
Mar 18, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

badgerstrader

New Member
Joined
Mar 18, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Do you want to get rid of the formula completely?
 

badgerstrader

New Member
Joined
Mar 18, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
In that case just add this line as shown
VBA Code:
rng.Formula = Result
rng.Value = rng.Value

End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top