convert complex formula into VBA

  • Thread starter Thread starter ddmchaas
  • Start date Start date
D

ddmchaas

When importing a .CSV file and after executing a "text to column"
macro, a formula is copied down every row in column L which analyzes
key cells in the row itself and the row below to determine if the row
should be deleted or kept as pertinent to performing further analysis
and places the word "keep" or "delete" in the cell. Then an operator
manually deletes the rows identified as such. My question is how do I
begin to convert this complex spreadsheet formula into appropriate VBA
statements to duplicate the process and eliminate the need for the
spreadsheet formula and the manual operation? Here's the formula:

=IF(OR(LEFT(B8,4)="tem:",LEFT(B8,4)="Job:"),"KEEP",IF(OR(CELL("TYPE",A8)="V",CELL("type",A7)="v"),"KEEP",IF(LEFT(B9,4)="Job:","KEEP","delete")))

TIA for any help...
 
Assuming you have column C clear, and your formula goes into C7 as it was
written:

Sub KeepDelete()
Range("C1:C" & Range("B65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(OR(LEFT(R[1]C[-1],4)=""tem:"",LEFT(R[1]C[-1],4)=""Job:"")," & _
"""KEEP"",IF(OR(CELL(""TYPE"",R[1]C[-2])=""V"",CELL(""type""," & _

"RC[-2])=""v""),""KEEP"",IF(LEFT(R[2]C[-1],4)=""Job:"",""KEEP"",""delete""))
)"
With Range(Range("C1"), Range("C1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("C1"), Order1:=xlDescending
Columns("C:C").Find(What:="delete", After:=Range("C1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("C1").EntireColumn.Delete
End Sub

HTH,
Bernie
MS Excel MVP
 
Bernie...I tried to decipher before testing your VBA...a few
questions...

After executing my existing "text to column" macro, the first "clear"
column is column L. Since the column is clear, why do you do the

Range("C1:C" & Range("B65536").End(xlUp).Row)­...

what would it do? The parsed data includes no columns with something
in every cell of the column.

Also, we would prefer NOT to sort the data as the existing order is
significant...

thanks again for your help...

DDMcH
 
DDMcH,

Try this version, which will insert a new column A, then delete it when
you're done. Note that the sorting only sorts based on the Keep / Delete
results, so the order is not really changed except for the rows being
deleted.

HTH,
Bernie
MS Excel MVP


Sub KeepDelete()
Range("A1").EntireColumn.Insert
Range("A1:A" & Range("B65536").End(xlUp).Row).FormulaR1C1 = _
"=IF(OR(LEFT(R[1]C[2],4)=""tem:"",LEFT(R[1]C[2],4)=""Job:"")" & _
",""KEEP"",IF(OR(CELL(""TYPE"",R[1]C[1])=""V"",CELL(""type""," & _
"RC[1])=""v""),""KEEP"",IF(LEFT(R[2]C[2],4)=""Job:""," & _
"""KEEP"",""delete"")))"

With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending
Columns("A:A").Find(What:="delete", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub


Bernie...I tried to decipher before testing your VBA...a few
questions...

After executing my existing "text to column" macro, the first "clear"
column is column L. Since the column is clear, why do you do the

Range("C1:C" & Range("B65536").End(xlUp).Row)­...

what would it do? The parsed data includes no columns with something
in every cell of the column.

Also, we would prefer NOT to sort the data as the existing order is
significant...

thanks again for your help...

DDMcH
 
Back
Top