VBA Setting Value As Constant

Herbiec09

Active Member
Joined
Aug 29, 2006
Messages
250
Hi Guys,

First question - How do i set/define a value in a certain cell, on a certain sheet (value can change) as a constant in excel vba so that I can perform an if statement against that function.

I want someone to come in an type a certain value in a certain cell, and then the vba to pick this up and perform an if on a column of data.

Second Question - If it finds that valuein that column, I would like it to copy the data to the right of that cell.

Many Thanks

Herbert
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
"define a value..(value can change) as a constant"

Can't be done.

But your code could refer to Range("certainCell").Value
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,113
Office Version
  1. 2013
Platform
  1. Windows
Herbie,

Your question is not 100% clear to me but this may help you.
Assuming That your 'certain cell' is A3 in sheet 1, that , your 'set' value is a value you have set/defined by entering it somewhe that you can reference it to such as D3 and you wish to copy A3 to B3 if it is the correct value then.......
Use something like the following code in the Worksheet_Change event of sheet1.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'do nothing if changed cell is not A3
If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
'Do nothing if mutiple cells are changed  at the same time
If Target.Count > 1 Then Exit Sub
' if it is cell A3 check the value compared to your 'trigger value' in say D3
'and copy value accross to next column
Application.EnableEvents = False   'stop this code from disappearng up its own backside!
If Target.Value = Range("D3").Value Then Target.Offset(0, 1).Value = Target.Value
Application.EnableEvents = True 'allow this code to function normally
'otherwise do nothing
End Sub

Adapt it to suit your needs.

Let me know if that helps.

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,127,445
Messages
5,624,817
Members
416,056
Latest member
VARSHA V VASWANI

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