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?

 

[hnsd24_CN]

G1

=if(C1="SUNNY",I1,"")

Conditional formats cannot do this. If you use VBA, please let me know,if column "C" not contains the word "SUNNY",how to deal with column "G"

 

[NeoSez]

G1

=if(C1="SUNNY",I1,"")

Conditional formats cannot do this. If you use VBA, please let me know,if column "C" not contains the word "SUNNY",how to deal with column "G"

Hi hnsd24_CN .
I guess I will need to use VBA.
If column "C" not contains the word "SUNNY", then do nothing in column "G". Thank you for our help.

 

[hnsd24_CN]

Starting with the second row of the worksheet, I think the first row must contain a title

Sub test()
    Dim i As Integer
    Dim r As Integer
    Dim arr() As Long
    r = Range("C65536").End(xlUp).Row
    ReDim arr(1 To r - 1)
    For i = 2 To r
        If Range("C" & i) = "SUNNY" Then
           arr(i - 1) = Range("I" & i)
        Else
           arr(i - 1) = Range("G" & i)
        End If
    Next
    Range("G2:G" & r) = Application.Transpose(arr)
End Sub

 

[My Aswer Is This]

Try this:

Sub Move_Data()
'Modified  10/27/2020  3:50:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 To Lastrow
    If Cells(i, "C").Value = "SUNNY" Then Cells(i, "G").Value = Cells(i, "I").Value: Cells(i, "I").Value = ""
Next
Application.ScreenUpdating = True
End Sub

 

[NeoSez]

Starting with the second row of the worksheet, I think the first row must contain a title

Sub test()
    Dim i As Integer
    Dim r As Integer
    Dim arr() As Long
    r = Range("C65536").End(xlUp).Row
    ReDim arr(1 To r - 1)
    For i = 2 To r
        If Range("C" & i) = "SUNNY" Then
           arr(i - 1) = Range("I" & i)
        Else
           arr(i - 1) = Range("G" & i)
        End If
    Next
    Range("G2:G" & r) = Application.Transpose(arr)
End Sub

Thank you for providing the code. However, I ran into an runtime error 13, type mismatch. Not sure why. Is it because it starts at row 5? I changed the range from C65536 to C1000, for the test.

   arr(i - 1) = Range("G" & i)

 

[Fluff]

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

 

[NeoSez]

Try this:

Sub Move_Data()
'Modified  10/27/2020  3:50:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 To Lastrow
    If Cells(i, "C").Value = "SUNNY" Then Cells(i, "G").Value = Cells(i, "I").Value: Cells(i, "I").Value = ""
Next
Application.ScreenUpdating = True
End Sub

My Aswer Is This - Thank you​

This code worked in moving the entire cell content, but I only want the date. How do I change it to only extract the date (mm/dd/yyyy) at the top of that cell?

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?

 

[My Aswer Is This]

My Aswer Is This - Thank you​

This code worked in moving the entire cell content, but I only want the date. How do I change it to only extract the date (mm/dd/yyyy) at the top of that cell?

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?

In your original post you said: I would like to extract the date from column "I"
You never mentioned there was more then just a Date in the cell.
Extracting just the date is not something I can do. There may be 5,000 different variations of how a date might look: Like Susan went to the birthday party which was on Monday. Or Susan went to the party which was on Sunday January 14,2020 or maybe Sunday 4/7/1930. But now maybe someone else on this forum may have a answer.

 

[My Aswer Is This]

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