Need help with VBA with If Then

[Bond00]

This is the code

Sub LoopThruBooks1()
    Dim p, f, s, a, r, a1, a2, a3, a4, a5
    p = ThisWorkbook.path & "\All Quotes\"
    f = Dir(p & "*.xls*")
    s = "Enter Info for Quote"
    a = "C18"
    a1 = "C3"
    a2 = "C13"
    a3 = "C19"
    a4 = "C29"
    r = 1  'The row is starts on (binary)0 = row 1 and 1 = row 2 etc..
    Do While f <> ""
        r = r + 1
        Range("B" & r) = GetValue(p, f, s, a)
        Range("C" & r) = GetValue(p, f, s, a1)
        Range("D" & r) = GetValue(p, f, s, a2)
        Range("E" & r) = GetValue(p, f, s, a3)
        a5 = GetValue(p, f, s, a4)
        If a5 <> "" Then
            Range("J" & r) = GetValue(p, f, s, a4)
        End If
        f = Dir()
    Loop
End Sub

for where a4 is i'm trying to do a test to see if that cell is blank before filling the value. couldnt get it to work. Also that cell has a formula in it, its a if then to either fill it with a text value or make it = ""
this code outputs cell data into 1 sheet from many workbooks. i just want it to skip putting a value in the J column if there is nothing in C29 in the sheet aka a4

 

[JLGWhiz]

You currently have a4 declared a variant and a5 is not declared so it automatically becomes a variant. You would need to know what a5 is returning as a value when a4 = "". It could be "Empty" is the value, in which case you would need to:

If Not IsEmpty(a5) Then

 

[Bond00]

alright thanks

 

[JLGWhiz]

alright thanks

You're welcome,
regards, JLG

 

[Bond00]

You're welcome,
regards, JLG

i tested that out and it doesn't test positive for isempty because it has a function in it. So its blank but not really because of the code. i would have to do something like a length test or =CODE(C30)>0 instead?
a5 = c30 btw

 

[Bond00]

this works, but is there something more efficient? I want to make sure it runs quick because it will have to scan through a lot of files. so the less cpu cycles the better
If GetValue(p, f, s, a5) <> 0 Then

If i don't have this it fills in 0's in the fields instead of leaving it blank when the cell in the doc is blank. i also have a column with dates and if thats blank it puts 1/0/1900 date for 0.

 

[JLGWhiz]

The If statement, in and of itself, will not significantly reduce the processing time. The Loops and the amount of data they must loop through is what consumes the time. You would need to completely re-write the code into a different method to speed it up and offhand, I don't really have any recommendations.