VBA question to loop through data of a specific column

[kidult7]

Hi everyone,
I am very new to VBA , basically this is what I am trying to achieve:

I am trying to locate and loop through the data with a sepefic month.
Now with the find function, i can locate the column with the for the month header. How can I find the "real range" I need from the next row onwards till the last row.
For example , with below, current find.address function will be at A3, the column I will need to look for will be A4 ( A3+1) till the last column of A with data.

any input will be appreciated. Thanks


Sub test()

Dim locatem As Range
Dim Frequency As Range
Dim currentmonth1 As String
Set ws = Worksheets("Current KIs")

currentmonth1 = Sheets("Instruction").Range("B6").Value
With ws.Range("3:3")
Set locatem = .Find(currentmonth1, LookIn:=xlValues)

End With



End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

With Ws.Range("3:3")
   Set locatem = .Find(currentmonth1, LookIn:=xlValues)
End With
If Not locatem Is Nothing Then
   Ws.Range(locatem.Offset(1), Ws.Cells(Rows.Count, locatem.Column).End(xlUp)).Copy
End If

 

[kidult7]

thanks for your respond, Perhaps below will be a better demonstration.
Basically what I need is loop through the specific column based on "current month"

I have set the current month to be dynamic:
so a condition need to set for Y/N value based on the column of the month For example, if it's Nov, then they will loop though the Nov 2020 column to determine the value underneath it till last row

should I set up the loop through range as locatem.Offset(1), Ws.Cells(Rows.Count, locatem.Column).End(xlUp))? or there should be other smarter way?

Thank you so much in advance

Nov 2020	Dec 2020	Jan 2021	Y/N
R	G	R
Y	G	G
G	G	Y
G	G	G
G	G	G
G	G	G
NA	R	G
G	G	G

 

[Fluff]

What is the condition & what column should the Y or N go in?

 

[kidult7]

Condition will be based on the conditional formatting previously set on the table with the traffic light
basically I want to put "Y" if traffic light icon is ( non-green) , ie. RedTrafficLight or YellowTrafficLight. and "N" for GreenTrafficLight. on a particular column BE accordingly.


I haven't tried myself if this is workable solution yet, but i know the function for traffic lights are as following..
.Icon = xlIconGreenTrafficLight

Sorry in advance if I sound silly

 

[Fluff]

In that case I suggest you start a new thread, giving full details, as this has nothing to do with your original question.