VBA - Changing the value of a cell when another cell in the same row is changed

[KellyTheKid]

Hi, I am new to VBA and I have been learning from google and these forums. I am stuck on a problem and I have no clue how to address it; I am hoping someone could help me.

I Have a named Table "Log" and two columns in question; Column "Site" (Column Q) and Column "Country" (Column T). Column "Site" has a validation of five values (DEUBAR,DEUBER,DEUSAL,FRAPAR,FRALAD). These are country and location codes. I would like to update Column "Country" when the user changes the value of any cell in Column "Site" with the first three characters of the value (either DEU or FRA). My table will continue to grow so I need this code to apply to each new added row. I have been playing with the Worksheet_Change(ByVal Target As Range) sub, but I am so lost. Any assistance will be much appreciated.

 

[Roncondor]

I think you are overcomplicating it. You can
a) just have a formula directly in the spreadsheet pulling the first 3 characters using LEFT function - or -
b) using worksheetfunction.LEFT within the VBA

In general I am opposed to using worksheet_change unless absolutely necessary since
a. slows down execution of your spreadsheet and
b. has things going on behind the scenes that user is not aware of

 

[KellyTheKid]

I think you are overcomplicating it. You can
a) just have a formula directly in the spreadsheet pulling the first 3 characters using LEFT function - or -
b) using worksheetfunction.LEFT within the VBA

In general I am opposed to using worksheet_change unless absolutely necessary since
a. slows down execution of your spreadsheet and
b. has things going on behind the scenes that user is not aware of

Thanks for your reply Roncondor. I was using formulas before but I had to leave empty rows with no data so excel could remember the formula for the column. Once I addded VBA code to insert a new row on demand, excel would not apply the formula when a new row was inserted. I am curious however, regarding the VBA entry you propose on option b). I have the vba code for the left function; how to you make it so when I change a cell in column Q, the corresponding row cell in column T is updated with the left statement?

 

[Roncondor]

when you insert the row, you can copy down the formula from the row above/

something along the lines of the below (where Yourfromrange is a cell (or rangename) that contains the formula and YOURTORANGE is a cell (or preferably a rangename that contains where you want it copied

Range("YOURFROMRANGE").Cells(1).Copy
Range("YOURTORANGE").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False