1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - insert row in a list if the next cell content does not meet a condition

Discussion in 'Business Applications' started by Vasco da Gama, Sep 7, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    I have a excel file with 365 worksheets, each worksheet has the temperature and humitidity of one day for each 30 minutes of that day. Each worksheet has the name dd-mm-yyyy.
    But as I get the data from the web, sometimes the list skips some entries. I want to insert automatically a row in the list when the next row in not 30 minutes later. That row should have the time and a – sign in the temperature and humidity row. Preferably the macro would do this for all worksheets.


    Time Temperature (F) Temperature (ºC) Humidity (%)
    0:20:00 33,8 1,0 87
    0:50:00 33,8 1,0 87 I want to insert a row after this one with "1:20:00 - - -"
    1:50:00 33,8 1,0 87
    2:20:00 33,8 1,0 87
    2:50:00 32,0 0,0 93
    3:20:00 30,2 -1,0 93
    3:50:00 30,2 -1,0 93
    4:20:00 30,2 -1,0 93
    4:50:00 28,4 -2,0 100
    5:20:00 30,2 -1,0 93
    5:50:00 26,6 -3,0 93
    6:20:00 28,4 -2,0 93
    etc until
    23:50:00 37,4 3,0 87
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Can I ask you what your end result is? What are you ultimately trying to do here? And why 365 sheets? There should be no need with as many rows as Excel gives you (65536, more in upcoming versions).
     
  3. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    Hello,

    I have 365 worksheets because I collected weather data from the web with a macro, and to be easear to handle all the information I put a day data in each worksheet. So 365 days corresponds to information of a full year.

    To analyse all the information I need that each worksheet be ordered by the time.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Right, I understand that. My question is, how do you get the data from the web? Why not leave it all in one worksheet where you can work with it all? You could then add a formula column to it for returning the month of the date and use the Subtotal feature and show whatever you wanted to for each month.

    I do the same thing for some data of mine from a weather station. We could also possibly automate this if you give us more information about where you're getting your information and what you want to do with it in the end.
     
  5. blaqDeaph

    blaqDeaph

    Joined:
    Nov 22, 2005
    Messages:
    869
    You can use a macro to do this, assuming that the data is in the same cell on each sheet.

    Perhaps you can provide us with a sample of the worksheet that you're using.
     
  6. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    I'm getting the data from the website:
    http://english.wunderground.com/history/airport/LPPT/2006/1/1/DailyHistory.html
    http://english.wunderground.com/history/airport/EGLL/2006/1/1/DailyHistory.html
    This link is for Lisbon in Portugal and London in UK.
    I want to download the daily weather data (temperature and humidity) from 2004 until now.
    After that I want to calculate:
    - for how long a specific temperature occurred in both countries;
    - for how long a specific humidity occurred in both countries;
    - for how long a temperature range occurred in both countries;
    - for how long a humidity range occurred in both countries.

    Cheers,

    João
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Do they have their data in one central location? Meaning, all data is on a seperate page per a single day. Do they have a "view all" area? You can get the data into Excel no problem via code, you could probably automate the whole damned thing, but if it's one day per web page, it'll take about 3-6 hours to run everything, maybe more..

    For example, here is code for downloading the first and second days of January 2004 into seperate files...

    Code:
    Sub OpenOnlineFiles2()
        Dim wb1 As Workbook, wb2 As Workbook
        Set wb1 = Workbooks.Open("http://english.wunderground.com/history/airport/EGLL/2004/1/1/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA")
        wb1.SaveAs "C:\YourName1.xls", ReadOnlyRecommended:=False
        Set wb2 = Workbooks.Open("http://english.wunderground.com/history/airport/EGLL/2004/1/2/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA")
        wb2.SaveAs "C:\YourName2.xls"
    End Sub
    The code would have to be putting the data into seperate workbooks, but we could then consolidate. Seeing how long those two alone take to run, you can imagine how long it would take to get almost three years of data..
     
  8. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    Yes, it took a litle bit ... to download everything, but I have all the information in excel. Now I "just" need to treat all the data. What approach do you think its best? To gather in one worksheet everything (it will give a lot of work) or to get an automatic way of inserting and deleting rows (to put all worksheets with all the times) and then I can treat the data normally with formulas?
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well, it may just be better to put this information into an Access database, if you're talking about tens of thousands of records. If you only want the temperature and humidity for each day since January 1, 2004, that is only 1972 pieces of information. Plus you don't want to do very much with the data and you could manipulate it fairly easily, especially with Pivot Tables (if you don't know them - learn them, trust me, you'll love them once you get them working for you).

    So this begs the question, what exactly do you have? Did you use my code and input all 986 days? Are they all in seperate files? Are all of these files in the same directory? Are they the only files in that directory? If you got the information into a file, can you post it as an attachment (or a sample if it's too big)?
     
  10. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    Ok, I have 6 files named:

    Macro EGLL_2004.xls
    Macro EGLL_2005.xls
    Macro EGLL_2006.xls

    Macro LPPT_2004.xls
    Macro LPPT_2005.xls
    Macro LPPT_2006.xls

    The first 3 files are for London and the last 3 are for Lisbon. I compressed and attached the first 5 files. It won't let me put the 6th file. All the files are in the same directory and they are the only files on that folder.

    Cheers.
     

    Attached Files:

  11. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    I've attached the 6th compressed file (Macro LPPT_2006.xls).
     

    Attached Files:

  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'm putting together a solution, but would like to know, what version of Excel are you running?
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Okay, well this was developed in Excel 2003. The code is rather long, but it works splendidly for me. I hope it's what you were asking for. There is a couple issues that needs pointing out..

    365 days per year (sheets) {variable}
    49 rows of info per day (sheet)
    3 years of data
    2 data locations

    .. that roughly equals 107310 rows of information. Obviously that is too much for one sheet to handle (given that you are using Excel 2003 or prior, 2007 can handle this without any qualms but the code would need to be adjusted to accept the different file types/extensions), so there is a section of code written that will add a new sheet for any overflow data. When I ran this code on your files I was left with two sheets of data.

    The other issue is that there is a reference set to Microsoft Scritping Runtime (via VBE | Tools | References). I have this code in a sample file and linked to it, the reference is already set there. I ran the code in this file as well, and the first two sheets contain the data from the files you uploaded.

    Here is a link to the file:
    http://www.barresse.com/temp/MyDataExtraction_Ex.zip

    Here is the code (which goes in a standard module)..

    Code:
    Option Explicit
    
    'Set folder name to iterate through
    'THIS MUST BE ACCURATE!
    Private Const FOLDERNAME As String = "C:\Documents and Settings\Administrator.ZACKOLD\Desktop\MyFolder\"
    Private Const DNL As String = vbNewLine & vbNewLine
    
    Sub ExtractData()
    'Created by:    Zack Barresse
    'Date:          September 2006
    'Purpose:       Iterate through set folder and extract data from each
    '                   worksheet of each workbook found and compile it into a
    '                   single workbook for analysis.  Code is self-sustained
    '                   and tested in a Windows XP Pro OS with Office 2003
    '                   Professional SP2.
    'References:    Microsoft Scripting Runtime
    
        Dim FSO As Scripting.FileSystemObject
        Dim fsoFolder As Scripting.Folder
        Dim fsoFile As Scripting.File
        Dim wb As Workbook, wbTmp As Workbook
        Dim ws As Worksheet, wsTmp As Worksheet
        Dim rngCopy As Range, rngDate As Range
        Dim iCnt As Long, iWs As Long
        Dim eRow As Long, sRow As Long
        Dim LastRow As Long, i As Long
        Dim strPrompt As String, strTitle As String
        Dim blnDelete As Boolean, blnClose As Boolean
        Dim Msg As VbMsgBoxResult, msgDel As VbMsgBoxResult
    
        'Set error handler status
        On Error GoTo ErrHandler
    
        'Turn off application attributes
        Call ToggleEvents(False)
    
        'Set objects, folder (create if none) & variables
        Set wb = ThisWorkbook
        Set FSO = New Scripting.FileSystemObject
        If FSO.FolderExists(FOLDERNAME) Then
            Set fsoFolder = FSO.GetFolder(FOLDERNAME)
        Else
            FSO.CreateFolder (FOLDERNAME)
            strPrompt = "The spcified folder does not exist!" & DNL
            strPrompt = strPrompt & "Do you wish to create it now?"
            strTitle = "Create Folder?"
            Msg = MsgBox(strPrompt, vbYesNo, strTitle)
            If Msg = vbYes Then
                Set fsoFolder = FSO.GetFolder(FOLDERNAME)
                MsgBox "Folder created!" & DNL & "Exiting application!", vbInformation
                GoTo TheEnd
            Else
                MsgBox "No folder created!" & DNL & "Exiting application!", vbInformation
                GoTo TheEnd
            End If
        End If
    
        'Check for files
        If fsoFolder.Files.Count = 0 Then
            MsgBox "There are no files to process!", vbInformation, "ERROR!"
            GoTo TheEnd
        Else
            'If files found, ask if user wants to delete them after processing
            blnDelete = False
            strPrompt = "Would you like to delete the files when done" & vbNewLine
            strPrompt = strPrompt & "processing them?"
            strTitle = "Delete Files After Processing?"
            msgDel = MsgBox(strPrompt, vbYesNo, strTitle)
            If msgDel = vbYes Then
                blnDelete = True
            End If
        End If
    
        'Ensure a blank sheet is ready, set as variable
        Set ws = wb.Worksheets.Add(Before:=wb.Sheets(1))
    
        'Set headings for file import
        ws.Range("A1").Value = "Date"
        ws.Range("B1").Value = "Time"
        ws.Range("C1").Value = "Temperature"
        ws.Range("D1").Value = "Humidity"
    
        'Iterate through files in folder
        For Each fsoFile In fsoFolder.Files
    
            'Check for Excel files only (ver 2003 and prior)
            If LCase(Right(fsoFile.Name, 4)) <> ".xls" Then GoTo SkipFile
    
            'Check if file is open, set to variable
            If WbOpen(fsoFile.Name) = True Then
                Set wbTmp = Workbooks(fsoFile.Name)
                blnClose = False
            Else
                Set wbTmp = Workbooks.Open(FOLDERNAME & fsoFile.Name)
                blnClose = True
            End If
    
            'Reset sheet counter & variables, add file counter
            iWs = 0
            iCnt = iCnt + 1
            Set rngCopy = Nothing
    
            'Iterate through all sheets in file
            For Each wsTmp In wbTmp.Worksheets
    
                'Worksheet counter, also our row counter, and the end row
                iWs = iWs + 1
                eRow = wsTmp.Cells(wsTmp.Rows.Count, 2).End(xlUp).Row
    
                'Check if there is room for more data
                LastRow = ws.Range("A:A").Find(what:="*", After:=ws.Cells(1, 1), _
                                               searchorder:=xlByRows, _
                                               searchdirection:=xlPrevious).Row
    
                'If there is no more room, fit data and create a new sheet
                If LastRow + eRow > ws.Rows.Count Then
                    ws.Cells.EntireColumn.AutoFit
                    'Add new worksheet and headings if this is the case
                    Set ws = wb.Worksheets.Add(After:=wb.Sheets(1))
                    ws.Range("A1").Value = "Date"
                    ws.Range("B1").Value = "Time"
                    ws.Range("C1").Value = "Temperature"
                    ws.Range("D1").Value = "Humidity"
                End If
                sRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
                'Inform user of progress on status bar
                Application.StatusBar = "Processing sheet " & iWs & " of " & _
                                        wbTmp.Worksheets.Count & ", in file " & _
                                        iCnt & " of " & fsoFolder.Files.Count & _
                                      " total file(s)..."
    
                'Copy data to worksheet
                Set rngCopy = wsTmp.Range("A3:A" & eRow)
                i = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1
                rngCopy.Offset(0, 0).Copy ws.Cells(i, 2)
                rngCopy.Offset(0, 1).Copy ws.Cells(i, 3)
                rngCopy.Offset(0, 3).Copy ws.Cells(i, 4)
    
                'Add date to time value in column A and make it static and format it
                LastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
                Set rngDate = ws.Range(ws.Cells(sRow, 1), ws.Cells(LastRow, 1))
                rngDate.Value = CDate(wsTmp.Name)
    '            rngDate.FormulaR1C1 = "=" & Chr(34) & CDate(wsTmp.Name) & Chr(34) & "+RC[1]"
    '            rngDate.Value = rngDate.Value
                rngDate.NumberFormat = "dd-mm-yyyy hh:mm AM/PM"
    
            Next wsTmp
    
            'If user wanted to delete file, do it here
            If blnDelete = True Then
                fsoFile.Delete Force:=True
            End If
            If blnClose = True Then
                wbTmp.Close SaveChanges:=False
            End If
    
    SkipFile:
        Next fsoFile
    
        'Fit all columns and report to user the completion status
        If iCnt > 0 Then
            ws.Cells.EntireColumn.AutoFit
            strPrompt = "There were a total of " & iCnt & " file(s) processed."
            MsgBox strPrompt, vbInformation, "Completed!"
        End If
    
    TheEnd:
        'Reset application attributes
        Call ToggleEvents(True)
        Exit Sub
    ErrHandler:
        strPrompt = "An unexpected error has occured!"
        strPrompt = strPrompt & DNL & Err.Description
        MsgBox strPrompt, vbCritical, "ERROR!"
        Resume TheEnd
    End Sub
    
    Sub ToggleEvents(blnState As Boolean)
    'Created by:    Zack Barresse
    'Date:          September 2006
    'Purpose:       Toggle application properties for code efficiency purposes.
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState = True Then
                .CutCopyMode = False
                .StatusBar = False
            End If
        End With
    End Sub
    
    Function WbOpen(wbName As String) As Boolean
    'Created by:    Zack Barresse
    'Date:          September 2006
    'Purpose:       Check if a specified workbook is already open or not.
        On Error Resume Next
        WbOpen = CBool(Len(Workbooks(wbName).Name))
    End Function
    Let me know how it goes. I spent a considerable amount of time on it and would love to know if it works for you or not.
     
  14. Vasco da Gama

    Vasco da Gama Thread Starter

    Joined:
    Sep 7, 2006
    Messages:
    7
    Hello Zack,

    I've tried your program and it worked beautifully. I'm now analyzing the data with the pivot tables), that as you said, do a lot and save loads of time.

    If there is anything that I can help you with in the future, just let me know.

    I wish to thank you all the time you spent helping me with this macros, you're expertise has been very useful to my PhD.

    Cheers,

    João
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Great, I'm really glad you liked it and it worked for you. :)

    Good luck with your PhD!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/499122

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice