• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If And If Then For Each C In Range

I

indi visual

Member
How do I modify this to search For Each C In Range?


This code actually doesn't work, but I was hoping to get help with syntax so that it does.


Basically, if C3:C4 are the same, and A2 contains the word "Required", then I would like to run a code on A3:A4. The problem is I'm not sure how to write this to search for every C in Range.


Sub Find_NotRequired_That_Should_Be_Required()

Dim C As Range

For Each C In Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)


If Range("C2").Value = Range("C3:C4") _And

Range("A2").Value = "Required" Then


'''On A3:A4 I would like to search and replace the word "Not Required" with "Required"

Selection.Replace What:="Not Required", Replacement:="Required"


ElseIf Range("A2").Value <> "Required" Then


Next C


End If


End Sub
 
Don't define your range names (when you want it to change). Since you are looping through based on "C", you need to use that as your reference point, using the offset function like so:

Code: 
Sub Find_NotRequired_That_Should_Be_Required()

For Each C In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


ColC = C.Offset(0, 3).Value

If C.Offset(1, 3).Value = ColC And C.Offset(2, 3).Value = ColC Then

Range(C.Offset(1, 0), C.Offset(2, 0)).Replace "Not Required", "Required"

End If


Next C


End Sub
 
Man how long did it take you to come up with that?


How long have you been doing this - did you go to school for this or did you lego your way through visual basic like I'm doing?


Amazing.


Thanks Luke.
 
You're very welcome.

I learned about VB 6 years ago. Started out as the low-paid intern tasked with lots of repetitive tasks, knew there had to be a better way. So yea, self taught, recording macros to see how to write things, and browsing the excel forums. And the more I learn, the more I learn how much I don't know. ;)
 
@Luke


Luke the code you provided me works perfect. However working with it I realized my initial approach could be more efficient. The code I've written that you got me started with makes sense (and should work in theory), but somewhere in the midst of it I'm doing something wrong. Your help was not in vain, and as you'll see I took what I learned from you and tried to get fancy and take it a little further.


Here is my link for my workbook for a more clear example.


http://www.iandmyself.me/example.workbook.xlsm


My objective for this code is to match 3 criterias before marking as "<Required*>:


Since "A8" is marked as "<Required*>"


And "A6" & "A7" is false (the formula I placed in it was =ISTEXT) ''it is important what I'm about to mark does not contain text


And "A6 & "A7" are in that same folder (column D)


Then


Mark "A6" & "A7" as "<Required*> as well.


If this code is successful, it will change "A14" & "A19" the same way because they share the same folder in Column D with another cell that's marked "<Required*>.


Here is the code beneath (although the workbook link I've provided will definitely make more sense of this).


I appreciate and welcome any and all advice from anyone, please have a look.


Sub Find_Number_And_Mark_Required_If_ColumnD_Text_Is_Same_And_Is_Not_Already_Marked()


For Each C In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

SpecialCriteria = "*<Required*>"


''''''''''''''''''''''''''''''''

' if D2 = D3

' and C = specialcriteria

' and C3 = false

' then exectue action on A3

''''''''''''''''''''''''''''''''

If _

C.Offset(0, 3).Value = C.Offset(1, 3).Value _

And C Like SpecialCriteria _

And C.Offset(1, 2).Value = False _

Then

'execute this action on cell A3

'encapsulate number in arrows with Required on left

C.Offset(1, 0).Value = "<Required " & C.Offset(1, 0).Value & ">"


'''''''''''''''''''''''''''''''

' elseif D2 = D3

' and A3 = specialcriteria

' and C2 = false

' then execute action on A2

''''''''''''''''''''''''''''''''

ElseIf _

C.Offset(0, 3).Value = C.Offset(1, 3).Value _

And C.Offset(1, 0).Value = SpecialCriteria _

And C.Offset(0, 2).Value = False _

Then

'execute this action on cell A2

'encapsulate number in arrows with Required on left

C = "<Required " & C & ">"


End If

Next C

End Sub
 
Your first Small but important problem is that A16 had < Required 6583> not <Required 6583> and hence is didn't match your criteria of *<Required*>


Try this code:

[pre]
Code: 
Sub Find_Number_And_Mark_Required_If_ColumnD_Text_Is_Same_And_Is_Not_Already_Marked()

Dim C As Range, D As Range
Const SpecialCriteria = "*<Required*>"

For Each C In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If C.Offset(, 2).Value Then
For Each D In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If D.Offset(, 3) = C.Offset(, 3) Then D.Value = "<Required " + D.Offset(, 1).Text + ">"
Next
End If
Next C
End Sub
[/pre]
 
What I learned : )


I didn't know C meant Cell lol, very helpful to know it actually meant column.

I didn't know you could leave out the up down offset piece (, 2).

I didn't know you could use two consecutive For Each's.

I didn't know you could substitute & for +

I didn't know to specify SpecialCriteria as a Const


5 things I picked up thanks Hui.


One more question though...


When you specify the Const... how does this work exactly?


I guess what I'm needing to know is at what point is the code looking for the Const (or knows it's there) how does the code read the the Const?


Just wanted to know so I can properly use it in the future.
 
Technically C doesn't mean Cell

You could have just as easily said for each Zebra in Range("A2:A10")

C is just a name for each object in the container which in this case is a Range and each object is a Cell.

If you had specified for Each C in Range("a1:z1").Columns, then C would be each Column


For / Next just establishes a Loop based on some criteria, I don't believe there is a limit of how deep they can be nested


Did the code do what you wanted ?
 
Yes the code worked perfect on my example workbook. But on my real messy workbook it actually changed every cell in Column "A" for some reason. It also took the number values from column B and added it to Required. Column B actually serves no purpose (I left it there to set it up for the next piece of code I plan to work on - but I had to leave column B there in reference to all of the offsetting and such). As for the code you provided, I learned a lot from it, but it's my fault - I just can't seem to explain what I'm asking for quite right without making it sound complicated.


I wish to wrap the <Required*> with the asterisk being the value that was there originally around all numbers (not text) in column A, if at least one of the numbers are already wrapped in <Required*>, and if they share the same text content in column D (folder name).


Maybe this will help explain a bit (because this is what I'm actually using this for).


I'm using this to rename files on my pc.


Think of files in a folder. If one file in a folder is wrapped in <Required*>, I want all of the files in that folder to be wrapped in <Required*> (with the original value to remain represented by the asterisk that is).


IE...


If one name in Column A is wrapped with <Required*>, then I would like all names that share the same Column D name to be wrapped in <Required*>.


Think of Column D as the folder, and Column A as the file name.
 
I would delete this entire post and start over with a clean slate if I could.


I think I poisoned this with too many details : (
 
Officially Solved! -Got the code tweaked and working (Thanks Hui and Luke respectively).


Glad to have this one over with I learned a lot.


If you plug this in the workbook example link I provided above, you'll see what my aim was.


Sub got_it()

Dim A As Range, C As Range, D As Range

Const SpecialCriteria = "<Required*>"


For Each A In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

If A Like SpecialCriteria And A.Offset(, 2).Value = True Then

For Each D In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

If D.Offset(, 3) = A.Offset(, 3) And D.Offset(, 2) = False Then D = "<Required " & D.Text & ">"

Next

End If

Next A

End Sub
 
You must log in or register to reply here.
Back
Top