VBA Help with IF statement

[sweetness34]

Hi i need help with simple coding. I cant seem to come up with the right code for the program to understand.
here is my code

    If  ***       Then
 
Cells.Find(What:="Non-Conveyor Piping", SearchDirection:=xlNext, LookAt:=xlWhole).Select
Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert shift:=xlUp
Else

what i want to happen is search the spreadsheet for "Non-Conveyor Piping" and if it exists then execute the code. if it does not, then simply ignore the code and procede.

Thanks

 

[Joe4]

Not sure. But, if you can implement the suggestion I gave you, this code should no longer be needed, right?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[sweetness34]

unfortunately not :/ . Moving the data to new tabs and deleting the excess works great. But i still get an error with the "on error" you helped me with. The issue is when it searches for Conveyor Piping, Non-Conveyor Piping, and Pipe Supports. If it finds 2 of the 3 then it doesnt have a problem, but if it only finds 1 then there is an error that pops up. I need to get around this error so it runs if there is only 1 of the 3 present.

 

[Joe4]

unfortunately not :/ . Moving the data to new tabs and deleting the excess works great. But i still get an error with the "on error" you helped me with.

I don't think we are understanding each other. The methodology I am proposing would REPLACE the code I first helped your write. If we get that to work, the code first proposed shouldn't even be necessary.

Let's take a step back to make sure we are on the same page.

If I understand you correctly, you need to "look up" the values from column H on the first page (raw data) and place them into the appropriate place on sheet 2. As I see it, it looks like the problem with that is that the column values in columns C and D for each associated values in column H do not appear on that row, but the one above it.

Now, we could try to make a complex VBA code to cycle through each row and determine where each value should go on sheet 2 (which is the path you were first headed down). My last proposal is that instead of doing that, take your raw data and collapse it down into a nice concise lookup table, where there is a row for every value in column H that has the associated field values populated in columns C and D, without any of the others rows that are not contributing to the solution. If we do that, then we should be able to simply use VLOOKUP functions on sheet 2 to look up the values that you need from our lookup table.

Am I understanding the essence of your dilemna?
Do you understand the mnethodology I am now proposed, in lieu of the code we first started working on?

 

[sweetness34]

Yes you are correct on whats going on except the numerical value is actually one row below, not above, but other than that you have the concept correct. But I think you have maybe missed a part. I like your idea but the part I dont understand is how you plan on taking the numerical values from the raw data sheet into our "working" tab that you proposed, because the values are sums. Correct me if I'm wrong but I believe the error needs to corrected before. The reason I think it needs to be fixed instead of replaced is because the value being copied is a sum of the previous rows. So if it were to be copied on a new sheet and deleted like you advised, how would the sum be calculated? Regardless, Conveyor Piping needs to be summed on the raw data sheet ONLY IF IT EXISTS. If it does then execute code. If it does not exist then that is ok. Regardless if Conveyor Piping's existance it should move on to Non-Conveyor Piping and sum those values, but again only if it exists. Same for Pipe Supports. So how do you take the selection of data(the sum has not been yet caluclated), then copy to a new tab, then delete the excess data you talked about to get the short table, then sum the data that has now been deleted? My "debugging" error occurs durring the summation which would need to be prior to the copying to a new tab and deleting excess. I hope that clears things up?

 

[Joe4]

OK, let's start with this.

Is this or is this not an accurate portrayal or your data (http://www.mrexcel.com/forum/showpost.php?p=2841266&postcount=6)?
Did you leave anything out?
Are there any errors on your raw data sheet to start with?
Are you saying that the values in column H are a sum of something?

So if it were to be copied on a new sheet and deleted like you advised, how would the sum be calculated?

It doesn't need to be. When copying over your Raw Data to the Working tab, use Copy => Paste Special => Values. This copies the values, not the formulas. So any formulas will be converted to hard-coded values. So then deleting the original components of the SUM will not affect the value in the row (since it is no longer dependent upon it).

 

[sweetness34]

it is but simplified a little. this is a little more accurate. that is how the summations works. columns A,B,E,F,G have data but is 100% irrelevant.

___A__|__B_|_______C_______|__D___|___E___| ____F___|___G___|___H
___________| Conveyor Piping | Prep |________|________|_______|__10_
___________| Conveyor Piping | Prep |________|________|_______|__10_
___________| Conveyor Piping | Prep |________|________|_______|__10_
___________|_______________|____|________|________|_______| 30
___________| Conveyor Piping | Rev |________|________|_______|__10_
___________| Conveyor Piping | Rev |________|________|_______|__5__
___________| Conveyor Piping | Rev |________|________|_______|__5__
___________|______________|_____|________|________|_______| 20
___________|Non-Conveyor | Prep |________|________|_______|__5__
___________|Non-Conveyor | Prep |________|________|_______|__5__
___________|Non-Conveyor | Prep |________|________|_______|__2__
___________|Non-Conveyor | Prep |________|________|_______|__3__
___________|____________|_____|________|________|_______| 15

i understand the paste values part and that it is no longer a formula.

If by errors in the raw data you mean VBA execution then yes. If you mean the actual spreadsheet data then no.
and yes the column H values at the end of each category are a sum but the values on the same line as ones with text are strictly values, no formula.

 

[Saagar]

By skimming the thread, I notice that your current problem is trying to locate the total row for the "conveyor piping" part.

Looking at your code in http://www.mrexcel.com/forum/showpost.php?p=2842631&postcount=13, I see that you are actually inserting a blank row and adding the "sum" formula in Column H.

After the line "Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))" in your code you may put a piece of code to put the string "Conveyor Piping Total" in column C.

That way you can search for the string "Conveyor Piping Total" and find only the total rows.

Hope this helps.

 

[sweetness34]

By skimming the thread, I notice that your current problem is trying to locate the total row for the "conveyor piping" part.

Looking at your code in http://www.mrexcel.com/forum/showpost.php?p=2842631&postcount=13, I see that you are actually inserting a blank row and adding the "sum" formula in Column H.

After the line "Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))" in your code you may put a piece of code to put the string "Conveyor Piping Total" in column C.

That way you can search for the string "Conveyor Piping Total" and find only the total rows.

Hope this helps.

True, but that doesnt solve the issue with the error I get when it searches for 3 strings and only finds 1

 

[Joe4]

If have a tab for "Raw Data" and a blank tab for "Working", the following code will copy the data from "Raw Data" into "Working" and make a nice summary table that looks like this (note I only listed the important columns here):

C                  D         H
Conveyor Piping   Prep      30
Conveyor Piping   Rev       20
Non-conveyor      Prep      15

Sub MyCopy()
 
    Dim myLastRow As Long
    Dim i As Long
    Dim myPrevID As String
 
    Application.ScreenUpdating = False
 
'   Copy Raw Data values to Working tab
    Sheets("Raw Data").Activate
    Cells.Copy
    Sheets("Working").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'   Find last row with data in column H
    myLastRow = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all rows, starting from last row up to row 2 (assuming data starts in row 2)
    For i = myLastRow To 2 Step -1
'   Check for value in column H
        If Cells(i, "H").Value > 0 Then
'   Check for entries in columns C and D ...
            If Len(Cells(i, "C").Text & Cells(i, "D").Text) = 0 Then
'   ... if blank, copy down values from row above
                Cells(i, "C") = Cells(i - 1, "C")
                Cells(i, "D") = Cells(i - 1, "D")
'   ... else capture entries in single string for later comparison
                myPrevID = Cells(i, "C").Text & Cells(i, "D").Text
            Else
'   ... check to see if values in C and D are same as row below, and if so, then delete the row
                If Cells(i, "C").Text & Cells(i, "D").Text = myPrevID Then Rows(i).EntireRow.Delete
            End If
        End If
    Next i
            
    Application.ScreenUpdating = True
    
End Sub

Now, if that looks like it will work for you, our next step would be to make this lookup table a little more "VLOOKUP friendly", by combining the columns C and D into a single column and name the range.

 

[sweetness34]

If have a tab for "Raw Data" and a blank tab for "Working", the following code will copy the data from "Raw Data" into "Working" and make a nice summary table that looks like this (note I only listed the important columns here):

C                  D         H
Conveyor Piping   Prep      30
Conveyor Piping   Rev       20
Non-conveyor      Prep      15

Sub MyCopy()
 
    Dim myLastRow As Long
    Dim i As Long
    Dim myPrevID As String
 
    Application.ScreenUpdating = False
 
'   Copy Raw Data values to Working tab
    Sheets("Raw Data").Activate
    Cells.Copy
    Sheets("Working").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'   Find last row with data in column H
    myLastRow = Cells(Rows.Count, "H").End(xlUp).Row
 
'   Loop through all rows, starting from last row up to row 2 (assuming data starts in row 2)
    For i = myLastRow To 2 Step -1
'   Check for value in column H
        If Cells(i, "H").Value > 0 Then
'   Check for entries in columns C and D ...
            If Len(Cells(i, "C").Text & Cells(i, "D").Text) = 0 Then
'   ... if blank, copy down values from row above
                Cells(i, "C") = Cells(i - 1, "C")
                Cells(i, "D") = Cells(i - 1, "D")
'   ... else capture entries in single string for later comparison
                myPrevID = Cells(i, "C").Text & Cells(i, "D").Text
            Else
'   ... check to see if values in C and D are same as row below, and if so, then delete the row
                If Cells(i, "C").Text & Cells(i, "D").Text = myPrevID Then Rows(i).EntireRow.Delete
            End If
        End If
    Next i
 
    Application.ScreenUpdating = True
 
End Sub

Now, if that looks like it will work for you, our next step would be to make this lookup table a little more "VLOOKUP friendly", by combining the columns C and D into a single column and name the range.

i tried it and it didnt work exactly like that. how come we cant just do an if statement?
like .. IF ***have it search for conveyor piping here and if it exists***THEN ***execute code*** ELSE ***procede***