Macro for Copy & Paste with multiple conditions

[Jan Kalop]

I have worksheet (DATE) with a lot dates and related data. To find specific values, in cell I2 I enter date which is use to generatate in cell K5 that specialy search value which depends on the entered date. Most of the time I need to go through search quite a lot of dates which are listed daily in Column Y2:Y100 and I would like to copy and paste that value from cell K5 to cells in Column Z? , next to related date. Instead doing that with many macro specialy created for each induvidual entry I would like to have one macro to be able to do that automaticly every time i clik on single macro button. To avoid manually enter each date I have automated that with Combo Box (ActiveX Control). Please help because with single Copy & Paste I have no problem but in that scenario I hit the wall. I realize that, the solution would be very useful in many other occasions.
Thankyou....

 

[My Aswer Is This]

Let me see if I understand.

You want to load all the dates in Range("Y2:Y200") into ComboBox1

Then when you select a Date in the ComboBox you want Range("K5") to display the value from Column Z

So if you select 1/20/2019 in the ComboBox and if that Date is found in Range("Y20) Then the value from Range("Z20") entered into Range("K5"). Is that what you want.

If so use this script.

If not please explain again.


You would need these to scripts:

One script loads the ComboBox with value from Rage("Y20:Y200")

The other script needs no be put into your Combobox

Private Sub ComboBox1_Change()
'Modified  8/7/2019  9:37:29 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Set SearchRange = Range("Y2:Y200").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
Range("K5").Value = SearchRange.Offset(0, 1).Value
End Sub

This script loads the ComboBox with values.

Private Sub CommandButton1_Click()
'Modified  8/7/2019  9:37:29 AM  EDT
ComboBox1.List = Range("Y2:Y100").Value
End Sub

 

[Jan Kalop]

Very sorry, but unfortunately that's not what I am looking for.
I will try to explain the whole cycle from beginning as clear as I can.
Please be patient and thank you for your help.


To start from beginning: using Excel 2010


In Column Y2:Y100 everyday appear new set of dates and can vary from only a few to nearly sametimes 100 positions


In Column W2:W100 are numbers which counts how many entries is in ColumnY, from 1 to, depends how many is dates for that day.


Those numbers in Column W are only used for Combo Box to identify with help of formula Index and Match in cell I2, the date for cell I2.


Once the date is in cell I2, again formula Index and Match in cell K5 performing search in hidden part of the worksheet for the value related to the date in cell I2


Cells in Column Z2:Z100 have no formula and any value, and those cells supposed to be fill with data from cell K5 which corresponding to the related date in ColumnY, every time the date in cell I2 changes.


It can be done manually by Copy & Paste or simple VBA Copy &Paste Macro, from cell K5 to cell in ColumnZ?, but it have to be done every time when date in cell I2 change, because the value in cell K5 change too.


What I would like to have is to have one Macro with mulitple solution to be able with the every one clik Copy & Paste button, paste the value from cell K5 to cell in ColumnZ? with of course right corespondents to that date in cells in ColumnY.

 

[Jan Kalop]

Let me see if I understand.

You want to load all the dates in Range("Y2:Y200") into ComboBox1

Then when you select a Date in the ComboBox you want Range("K5") to display the value from Column Z

So if you select 1/20/2019 in the ComboBox and if that Date is found in Range("Y20) Then the value from Range("Z20") entered into Range("K5"). Is that what you want.

If so use this script.

If not please explain again.


You would need these to scripts:

One script loads the ComboBox with value from Rage("Y20:Y200")

The other script needs no be put into your Combobox

Private Sub ComboBox1_Change()
'Modified  8/7/2019  9:37:29 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Set SearchRange = Range("Y2:Y200").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
Range("K5").Value = SearchRange.Offset(0, 1).Value
End Sub

This script loads the ComboBox with values.

Private Sub CommandButton1_Click()
'Modified  8/7/2019  9:37:29 AM  EDT
ComboBox1.List = Range("Y2:Y100").Value
End Sub

Very sorry, but unfortunately that's not what I am looking for.
I will try to explain the whole cycle from beginning as clear as I can.
Please be patient and thank you for your help.


To start from beginning: using Excel 2010


In Column Y2:Y100 everyday appear new set of dates and can vary from only a few to nearly sametimes 100 positions


In Column W2:W100 are numbers which counts how many entries is in ColumnY, from 1 to, depends how many is dates for that day.


Those numbers in Column W are only used for Combo Box to identify with help of formula Index and Match in cell I2, the date for cell I2.


Once the date is in cell I2, again formula Index and Match in cell K5 performing search in hidden part of the worksheet for the value related to the date in cell I2


Cells in Column Z2:Z100 have no formula and any value, and those cells supposed to be fill with data from cell K5 which corresponding to the related date in ColumnY, every time the date in cell I2 changes.


It can be done manually by Copy & Paste or simple VBA Copy &Paste Macro, from cell K5 to cell in ColumnZ?, but it have to be done every time when date in cell I2 change, because the value in cell K5 change too.


What I would like to have is to have one Macro with mulitple solution to be able with the every one clik Copy & Paste button, paste the value from cell K5 to cell in ColumnZ? with of course right corespondents to that date in cells in ColumnY.

 

[My Aswer Is This]

Normally when using Vba we do not need a lot of formulas in the sheet.
Vba can do all the formulas and put the results in the cells.

And like this you said:

Those numbers in Column W are only used for Combo Box to identify with help of formula Index and Match in cell I2, the date for cell I2.

So we need a Vba script and then a combobox for something


I always like it when users tell me what information they have and what the end result of what you want is.

It seems as if you have decided you need a lot of formulas and a ComboBox and then some Vba code to do what you want.
​

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.