Code to search title labels, then manipulate specified column

[storemannequin]

I'm working on a code to search the first row of a data set for the word "label" and then search in that column for the letter "P", and for each "P" to put the word "This" in a column 2 to the left. This seems simple enough and I've given a crack at it, but the code isn't working properly. Any idea why not?

Sub tryout()
FC = Cells(1, Columns.Count).End(xlToLeft).<wbr>Column
FR = Cells(Rows.Count, 1).End(xlUp).Row
RowCount = FR - 1


For i = 1 To FC
If Cells(1, i).Value = "label" Then
With Cells(1, i).Resize(RowCount, 1)
For j = 1 To FR
If Cells(j, i).Value = "P " Then
Cells(j, i).Offset(, -2).Value = HSI
End If
Next j
End With
Exit For
End If

End Sub

 

[John Davis]

I'm working on a code to search the first row of a data set for the word "label" and then search in that column for the letter "P", and for each "P" to put the word "This" in a column 2 to the left. This seems simple enough and I've given a crack at it, but the code isn't working properly. Any idea why not?

Not sure if this is the problem but:

If Cells(j, i).Value = "P " Then<-----------you have an extra space with "p"
Cells(j, i).Offset(, -2).Value = HSI<----------I think this should be "HSI"

 

[storemannequin]

Not sure if this is the problem but:

If Cells(j, i).Value = "P " Then<-----------you have an extra space with "p"
Cells(j, i).Offset(, -2).Value = HSI<----------I think this should be "HSI"

In my data set, the 'P' is with a couple spaces after it. But something must be wrong with the code because I am getting error messages, like "next without For" etc. even though those key words are in the code. I appreciate the help.

 

[John Davis]

In my data set, the 'P' is with a couple spaces after it. But something must be wrong with the code because I am getting error messages, like "next without For" etc. even though those key words are in the code. I appreciate the help.

OK.

Then you need to either take out those spaces or indicate the exact amount in the code. From looking at it, there seems too be only one space.

I still think: HSI should be "HSI"

These lines:

End With
Exit For<------- What is the purpose of this line? I can't determine what it does.
End If

I think instead it should be:

End With
End If
Next i

 

[storemannequin]

Ok, so this code works when I use integers but not words:

Sub tryout()
FC = Cells(1, Columns.Count).End(xlToLeft).Column
FR = Cells(Rows.Count, 1).End(xlUp).Row
RowCount = FR - 1


For i = 1 To FC
If Cells(1, i).Value = "##" Then
With Cells(1, i).Resize(RowCount, 1)
For J = 1 To FR
If Cells(J, i).Value = "1" Then
Cells(J, i).Offset(, -2).Value = "HSI"
End If
Next J
End With
End If
Next i



End Sub

When I try to search for "P" the loop passes the correct column, but when I put "##" in that column header and search for that, the code executes exactly the way I wanted to.

Is there something else I need to do for string value to sync up?

 

[John Davis]

Ok, so this code works when I use integers but not words:




When I try to search for "P" the loop passes the correct column, but when I put "##" in that column header and search for that, the code executes exactly the way I wanted to.

Is there something else I need to do for string value to sync up?

This line:

If Cells(1, i).Value = "##" Then

If you put "p " then it looks for a value of "P space"

If you put "p " then it looks for a value of "p space space"

You just need to trim the cells with "p" values. You have took take out the extra spaces.

 

[storemannequin]

Thanks, my code works now. But one more question here:

Since only 1 column in my data set will have a title of "P" is there a way I can stop the loop right after it finds the first "P" in the loop of i?

Sub tryout()
FC = Cells(1, Columns.Count).End(xlToLeft).Column
FR = Cells(Rows.Count, 1).End(xlUp).Row
RowCount = FR - 1


For i = 1 To FC
If Cells(1, i).Value = "P" Then
With Cells(1, i).Resize(RowCount, 1)
For J = 1 To FR
If Cells(J, i).Value = "1" Then
Cells(J, i).Offset(, -2).Value = "HSI"
End If
Next J
End With
End If
Next i



End Sub

 

[John Davis]

Thanks, my code works now. But one more question here:

Since only 1 column in my data set will have a title of "P" is there a way I can stop the loop right after it finds the first "P" in the loop of i?

If you want to select the Column with the label "p", and there's only one, then maybe something like this instead of a loop?

With Rows("1:1")
    Cells.Find(What:="p", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows).Activate
End With

 

[storemannequin]

How can I set a variable to that column though? I've tried this, but have gotten an object defined error:

Sub findthis()


With Rows("1:1")
Set M = Cells.Find(What:="M", After:=ActiveCell, LookIn:=xlValues, LookAt:= _xlWhole, SearchOrder:=xlByRows).Activate.Row

End With


End Sub

Basically, I want to set the cell whose value equals to "M" to the variable 'M' to later use as my column reference in the next loop. For instance, here is my code doing the same task, the loop way:

Sub ThisMethodWorks()

FC = Cells(1, Columns.Count).End(xlToLeft).Column
FR = Cells(Rows.Count, 1).End(xlUp).Row
RowCount = FR - 1


For m = 1 To FC

If Cells(1, m) = "M" Then
Columns(m).Activate
Exit For
End If

Next m



For i = 1 To FC
If Cells(1, i).Value = "L" Then
Diff = i - m
With Cells(1, i).Resize(RowCount, 1)
For J = 1 To FR
If Cells(J, i).Value = "1" Then
Cells(J, i).Offset(, -Diff).Value = "HSI"
End If
Next J
End With
End If
Next i
End Sub

I'm a beginner programmer here, so forgive the stupidity! thanks