Conditional Formatting or VBA to extract date?

[NeoSez]

I would like to extract the date from column "I" and place that into column "G", but ONLY IF, column "C" contains the word "SUNNY".
Can I use conditional formatting to perform this?
If not, how would I write this in VBA?

 

[Rick Rothstein]

Assuming your dates in Column I are really of the full format mm/dd/yyyy and are the first text in the cell, then I think this macro should work...

Sub NeoSez()
  Dim LastRow As Long
  LastRow = Range("C" & Rows.Count).End(xlUp).Row
  Range("G2:G" & LastRow) = Evaluate(Replace("If(C2:C#=""Sunny"",LEFT(I2:I#,10),IF(G2:G#="""","""",G2:G#))", "#", LastRow))
End Sub

 

[NeoSez]

How about

Sub NeoSez()
   With Range("G2:G" & Range("C" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("If(" & .Offset(, -4).Address & "=""Sunny""," & .Offset(, 2).Address & ",if(@="""","""",@))", "@", .Address))
   End With
End Sub

Thank you Fluff, but I couldn't get it to work.

You said:
However, this code would work well for something else. I want to move the cell contents from SHEET2 column T to this SHEET1 column I, based on the value of Column A, which is just a number like 001. 002,etc. How do I modify this script for this?

I would need more details.
Like I want the name of the sheets.
When you say SHEET2 I'm not sure if that means the sheet is named "SHEET2" or if it's Sheet(2)

So please say something like Move from sheet named "Alpha" to sheet named "Bravo"
And when you say "Move" does that me copy to another sheet and delete from original sheet?

And you said:
based on the value of Column A, which is just a number like 001. 002,etc

So are you saying if the number is any number or must it be like "22"

What happens if the cell has "Susan is now 22 years old
And do you mean copy to the first empty cell in column I

Hi My Aswer Is This​

That is correct! The names of the sheets are SHEET2 & SHEET1. Yes, I would like to copy the full contents from SHEET2 column T to SHEET1 column I. The contents do not have to be deleted. Sheet 2 will be deleted after copy is completed, as there will be a new Sheet 2 every day.

The value of SHEET1 Column A, has a unique identifier number for every row, such as 001, 002- 000001, 000002, etc... which is also on SHEET2 ColumnF. So the contents will be copied based on this unique identifier number, replacing the current contents in SHEET1 column I .
I hope I provided enough information for you now. Thank you so much for your help.

 

[NeoSez]

Assuming your dates in Column I are really of the full format mm/dd/yyyy and are the first text in the cell, then I think this macro should work...

Sub NeoSez()
  Dim LastRow As Long
  LastRow = Range("C" & Rows.Count).End(xlUp).Row
  Range("G2:G" & LastRow) = Evaluate(Replace("If(C2:C#=""Sunny"",LEFT(I2:I#,10),IF(G2:G#="""","""",G2:G#))", "#", LastRow))
End Sub

Thank you Rick Rothstein!! That worked perfectly.

 

[My Aswer Is This]

Thank you Fluff, but I couldn't get it to work.

Hi My Aswer Is This​

That is correct! The names of the sheets are SHEET2 & SHEET1. Yes, I would like to copy the full contents from SHEET2 column T to SHEET1 column I. The contents do not have to be deleted. Sheet 2 will be deleted after copy is completed, as there will be a new Sheet 2 every day.

The value of SHEET1 Column A, has a unique identifier number for every row, such as 001, 002- 000001, 000002, etc... which is also on SHEET2 ColumnF. So the contents will be copied based on this unique identifier number, replacing the current contents in SHEET1 column I .
I hope I provided enough information for you now. Thank you so much for your help.

Sorry. But to be honest you need to start a new posting with this question. There are forum rules which state new questions should be posted in a new posting.
I will see your posting there and provide a solution there if I can.
Thanks

 

[NeoSez]

Sorry. But to be honest you need to start a new posting with this question. There are forum rules which state new questions should be posted in a new posting.
I will see your posting there and provide a solution there if I can.
Thanks

Agreed. I will do that right now.