Skipping blank cells in excel vba

[rolando]

hey folks,

I can't seem to figure out how to use an if statement to skip downloading data for a cell that is currently blank. What i did below is i tried to make the program think that if there is any text or number in cell b8 (defined as symbol4) then it should skip that entire downloading section of code.

Obviously greater than zero doesn't work since it is only text that should appear in cell b8. So the cell in b8 will either have text or be blank, so if it is blank then skip, if not then proceed with the downloader code. I know this should be a simple fix but can't figure out the proper way. Also i put "resume next" so it can proceed to the next part, which is identical lines of code. The entire program is basically 25 of that entire code snippet seen below. so at anytime maybe 10 of the 25 sections should actually be downloading the data, since 10 of the 25 cells will have data, the other cells will be blank.

Any input would be helpful. Thanks!

symbol4 = Range("b8").value

    
        If symbol4 < 0 Then Resume Next
           
   If symbol4 > 0 Then
   
  
   
    With Worksheets("Beta Historical").QueryTables.Add(Connection:= _
    "URL;http://ichart.finance.yahoo.com/table.csv?s=" & symbol4 & "&a=00&b=3&c=2009&d=09&e=10&f=2011&g=d&ignore=.csv" _
    , Destination:=Worksheets("Beta Historical").Range("as1"))
     .name = "import"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    
    
    End With
    End If

 

[Dryver14]

This is off the cuff but have you tried

If Range("B8").value = "" then exit sub

 

[rolando]

This is off the cuff but have you tried

If Range("B8").value = "" then exit sub

No i have not, I would try this now but i'm not sure how to tell it if there IS text then resume? something like this:

If Range("B8").value = "ANYTHING" then resume

obviously "ANYTHING" won't work but how does one instruct vba to resume, i was thinking something like IF NOT? thanks for your suggestion though.

 

[Dryver14]

It should just continue if there is anything in B8

if not try if then else

 

[rolando]

Thanks for that it actually worked now, i just modified the exit sub part

here's the code if anyone else is interested:

If Range("B8").value = "" Then

Else
  
   
    With Worksheets("Beta Historical").QueryTables.Add(Connection:= _
    "URL;http://ichart.finance.yahoo.com/table.csv?s=" & symbol23 & "&a=00&b=3&c=2009&d=09&e=10&f=2011&g=d&ignore=.csv" _
    , Destination:=Worksheets("Beta Historical").Range("it1"))
     .name = "import"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    
    End With
    End If

 

[Dryver14]

Glad we got it going

 

[rolando]

Oh one quick question while we're on this topic, when the data is downloaded it seems to all get crammed into the first column with commas separating the values, when instead each comma should be a new column. Apparently the fix for this was changing .Refresh BackgroundQuery:=True to .Refresh BackgroundQuery:=false

but for some reason it only works in some other sheet but not this one, even though its like literally an exact copy, any idea how to fix this?

 

[Dryver14]

Someone better than me can maybe fix this using VBA

You can do it on the sheet by using text to columns on the data tab

use a fixed point eg comma's and it should seperate things for you

 

[rolando]

Yeah, that's currently what i'm doing now, it would be ok if not for the fact that i would have to do that 30 times a day, so i'm trying to incorporate it all into one step, currently i have to press another macro button to do that process.

thanks for all your help

 

[Rick Rothstein]

Yeah, that's currently what i'm doing now, it would be ok if not for the fact that i would have to do that 30 times a day, so i'm trying to incorporate it all into one step, currently i have to press another macro button to do that process.

I've not worked with QueryTables before, so I am just guessing a little here, but I think inserting the following code line so it is the last line of code in what you posted will do that...

Worksheets("Beta Historical").Range("it1").TextToColumns Range("it1"), xlDelimited, , False, False, False, True, False, False