VBA Setting Value As Constant

[Herbiec09]

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

 

[mikerickson]

"define a value..(value can change) as a constant"

Can't be done.

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

 

[Snakehips]

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.

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