PDA

View Full Version : [SOLVED] VBA multiple Goal Seek depending and selection



Octopill
07-12-2016, 09:47 AM
Hello everyone,

I have almost 0 experience with VBA, and after spending 1 hour on google I could'nt find any "plug-in" code that would work for me.

Basically what I need is code that would goal seek all the cells that have been selected by the user, match them with a fixed value (from a single cell), by changing the cells adjacent to the selection.

It looks like this :

Columa A =Initial Date
Column B = number of days between initial Date and Final Date
Colum C = Final Date (Intial Date + Days)
Cell E2 = GoalSeek value

So user would select any cell from column C (final date), and the macro would change adjacent cells in column B to set selection to the GoalSeek Value in E2. Ideally the macro could be linked to a button so the user select -> click button -> tadaa

I am attaching a file if that helps.

Thanks a lot for your help :)

mdmackillop
07-12-2016, 11:51 AM
This will insert the number of days and addition formula as per your sample. You can call it from a button if you wish.


Sub Test()
For Each c In Selection
If c.Column = 3 And c.Offset(, -2) <> "" Then
c.Offset(, -1) = [e2] - c.Offset(, -2)
c.FormulaR1C1 = "=R[]C[-2]+R[]C[-1]"
End If
Next c
End Sub

Octopill
07-13-2016, 09:56 AM
Works incredible!

Thanks a lot man! Big time saver :)