If Statement help

  • Thread starter Thread starter alexm999
  • Start date Start date
A

alexm999

I have an IF statement:

If Range("a:a").Find(what:="DEV") Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If

Now i found out that I have more words to find in column A.

Here's what I need to look for:
DDC
DDCE
DEV
DTS
EXT
PUP
RIC
RIP
STD

Is there a way I can incoporate all these into 1 IF statement ?
Anyone have a bit of code to help? By the way, the above order is ho
it's generated in the file
 
Sub CheckValue()
varr = Array("DDC", _
"DDCE", _
"DEV", _
"DTS", _
"EXT", _
"PUP", _
"RIC", _
"RIP", _
"STD")
Dim rngCol as Range
Dim rng As Range, rng1 As Range
Set rng1 = Nothing
set rngCol = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
For i = LBound(varr) To UBound(varr)
Set rng = rngCol.Find(what:=varr(i))
If Not rng Is Nothing Then
Set rng1 = rng
Exit For
End If
Next
If rng1 Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If
End Sub
 
Looks great and im almost there...

The file where i access the information holds the names DDC, DDCE
etc... are in specific rows A15,A16,A17 and so on. I'd like my macro t
add a row if one of the names are missing. But if DDC,DDCE,DEV ar
mentioned but DTS is not there, i'd like it to add a blank row t
compensate for the missing info..
 
If its already on the worksheet why do a find. However the followin
code assumes you have a userform.

On Error Resume Next
Cells.Find(What:=UserForm1.textbox1.Text).Activate
If Err = "91" Then
Range("a16").EntireRow.Insert shift:=xlDown
End If

HTH
Charle
 
In my opinion, you are not even close to being almost there, because that
is a completely different problem. But if you feel you are, then continue
on on your own.

If you want to think the problem through and provide a complete problem
statement, then someone may be able to help if you feel you need it.

There is no sense attempting code until the real situation is revealed.

for consideration, if several rows are missing, then the ones that are
present are not in the assigned locations. It would be important to know
what the end result should be. Will the first occurance of any of these
values be in at least row 15 - if not, then what to do - insert rows until
it reaches 15? What you state implies that the list could be taken and
pasted starting in A15 and they would line up in the proper rows (not
suggesting this as a solution, but to identify where each should end up).
 
OK, i'll be more specific.

DDC, DDCE, DEV, DTS, EXT, PUP, RIC, RIP, STD are codes that appear in
column A (in the same order) starting in ROW A13. Sometimes DEV, DTS
and sometimes all of the codes are not generated because those
particular transactions never happened.

When my macro runs, it assumes that all codes are generated so it grabs
cell information for a "copy" & "paste" function to different
workbooks.

If one of the codes aren't there, it will throw off my macro and the
wrong data gets populated...

If you need examples, im attaching a text file which I open and start
it from Line 7. In the attached file, some of the codes are not
generated.

Attachment filename: 2.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=445553
 
Ok, I modified Tom's code to:



Sub CheckValue()
varr = Array("DDC", _
"DDCE", _
"DEV", _
"DTS", _
"EXT", _
"PUP", _
"RIC", _
"RIP", _
"STD")
Dim rngCol As Range
Dim rng As Range, rng1 As Range
Set rng1 = Nothing
Set rngCol = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For i = LBound(varr) To UBound(varr)
Set rng = rngCol.Find(what:=varr(i))
If rng Is Nothing Then
Set rng1 = rng
Range("a16").EntireRow.Insert shift:=xlDown
End If
Next
End Sub

This will now Insert a Row at Row 16. for each occurance of missin
"Data" IE: RIC and RIP of your example.

I hope this is what you are looking for.


Charle
 
Close but almost there. I need it to add a line after each code or to be
more precise, if for example code DEV is not there, i need it to add a
line to compensate for the missing CODE. My macro grabs cells from
fixed locations and if the file doesn't have a code (i.e., DEV, RIP,
ETC...) then my numbers are off...
 
When I open your textfile specifying starting at row 7, DDC ends up at row
13, not row 15.

but you said:
The file where i access the information holds the names DDC, DDCE,
etc... are in specific rows A15,A16,A17 and so on.
Click to expand...

Will DDC always be at 13? Will DDC always be there?

Do you want the macro to process all the drawers?
Close but almost there.
Click to expand...
Not really.
 
Ok you're right. Not really close...

DDC does start at 13, you're right.
DDC may or may not be there as well as the other Codes (EXT, DTS, RIP
etc...)

My macro grabs the numbers next to the cells at a "fixed" position
Lets say DEV is not there, then the rows will shift up. I need t
compensate for the missing rows.

Assuming all Codes are there then nothing happens, but as soon as on
of the codes is not there, a space should be added or a blank row so m
macro could grab the correct numbers for a copy and paste feature.

Thanks for being so patient with me..
 
In the "what" argument of the Find method include an array
of the values you want to search for. It will find the
first instance. You could also include a range of cells.

For example:
If Range("a:a").Find(What:=Array
("DDC", "DDCE", "DEV"...and so on)) is Nothing Then

HTH.

-Brad
 
Back
Top