Back Forum Reply New

Formula Explaination, Help PLEASE!

Hi All:
I have been using the following formula on one of my spreadsheets but I think it may be incorrect:  If (Val(Cells(tmprow, 6)) = 0 Or IsNull(Cells(tmprow, 11))) And (Val(Cells(tmprow, 12)) = 0 Or IsNull(Cells(tmprow, 12))) Then
       tmpsel = tmprow amp; ":" amp; tmprow       Rows(tmpsel).Select       Selection.Delete Shift:=xlUp
SO, if I am working in ROW 7 is this formula stating:
If the VALUE of  G7 is 0 or K7 is Blank AND L7 = 0 or Blank then select this row for deletion?
I am trying to fix a problem on the spreadsheet but need to understand this formula first.  The problem that has been brought to my attention is that if the value of G7 = RG545454 then the row is being deleted.  It seems that the above formula does not like the ALPHA in the cheque number (G7 is a cheque number).  If the cheque number in G7 is 545454 it is NOT Deleted?
Any help to assist me in understanding the formula or a suggestion to fix it would be GREATLY appreciated...
THANKS,
Mark

I think your code is OK except that G is the 7th column not the 6th.

Hi Andrew:
Sorry for the confusion.  Indeed my Cheque Number is located in Column F not G.  This may be why nobody else answered...
Was the rest of my decipher correct in regards to the K and L statements?
Any idea why the row would be deleted if F7 is RG545454 but will remain if F7 is 545454?
THANKS Again Andrew,
Take Care,
Mark

Val won't recognise RG545454 as a number. It stops reading the string at the first character it can't recognize as part of a number.

THANKS Andrew.  Any suggestions on revising the formula so it will recognize RG?  I don't really need it to know it is a number I just want the row to remain if there is a Value in there.
Here is the formula that I have in F7:
=IF('Input Sheet'!$A17="ER",'Input Sheet'!E17,"")
SO, IF the answer to the formula is anything but "" (blank) I would like the row to remain.
Any ideas?
THANKS,
Mark

Maybe try:Code:
If Len(Cells(tmprow, 6).Value) = 0 And (Val(Cells(tmprow, 12)) = 0 Or IsNull(Cells(tmprow, 12))) ThenTHANKS Andrew that seems to do the trick.  One LAST question regarding the formula.  If I was correct in stating that (tmprow, 12) represents Column L (L7 in my example) then I must have done something wrong because I have no data or formulas in that Column.  What my formula should state is that:
If there is a value in G7 and H7 is > 0 then Keep that row.  Is the formula below correct?
If Len(Cells(tmprow, 6).Value) = 0 And (Val(Cells(tmprow, 7)) = 0 Or IsNull(Cells(tmprow, 12))) Then
THANKS AGAIN for ALL your help Andrew,
Have a GREAT day,
Mark

To check that column G is empty and that column H contains a number greater than zero:

If Len(Cells(tmprow, 6).Value) = 0 And (IsNumeric(Cells(tmprow, 7).Value) And Val(Cells(tmprow, 7).Value)) = 0 Then

THANKS Andrew...  As ALWAYS you have been MOST helpful.
Have an EXCELLENT day,
Mark
¥
Back Forum Reply New