Posts by wonka1234

Re: VBA, pulling values together to do a comparison

Do I combine above code to my original?

Or is this fine on its own? (keep in mind im opening .txt files that are piped, (not delimited))

Running on its own I get error on :

[COLOR=blue]Set[/COLOR][COLOR=#333333] rWB1 = wb1.Sheets(1).Range("A2").CurrentRegion [/COLOR][COLOR=#006400]' may have to change sheet[/COLOR]

What is currentRegion?

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774447

Can you answer my previous question please. I don't understand this line, it errors for me

Code

errCheck = UserInput.FileDialogDictionary(fileNames)

Sorry Stephen , User Input is just a file picker functionI have in another module.
I can post that too, Name the module 'UserInput' :

Function FileDialogDictionary(ByRef file As Object) As Boolean ' returns true if the user cancels
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Dim item As Variant
Dim i As Long
'Create a FileDialog object as a File Picker dialog box.
file.RemoveAll 'clear the dictionary
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
'Use a With...End With block to reference the FileDialog object.
With fd
    'Use the Show method to display the File Picker dialog box and return the user's action.
    'The user pressed the action button.
    .Title = "Select Excel Workbooks" 'Change this to suit your purpose
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Microsoft Excel files", "*.xlsx,*.xls,*.csv,*.dat,*txt"
    If .Show = -1 Then
        'Step through each string in the FileDialogSelectedItems collection.
        For Each item In .SelectedItems 'loop through all selected and add to dictionary
            i = i + 1
            file.Add i, item
        Next item
        FileDialogDictionary = False
    'The user pressed Cancel.
    Else
        FileDialogDictionary = True
        Set fd = Nothing
        Exit Function
    End If
End With
Set fd = Nothing 'Set the object variable to Nothing.
End Function

Re: VBA, pulling values together to do a comparison

bumpy

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774371

I can't see where you define your file names? Do the two files have specific names or can they be anything?

They will change slightly, so no they wont always be the same name,

Re: VBA, pulling values together to do a comparison

thanks for your reply, where in my original code could I input this?

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774365

So the two source tables are in different files and you want the results in a third file?

Yes. Precisely. Unless you think of a better way of displaying?

Re: VBA, pulling values together to do a comparison

Perhaps this sample shows more detail.

Sheet 1 would be a workbook in reality, same with sheet 2.
and desired result would be my master sheet with the button.

Of course, my way may be abit longer, so if anyone has ideas on better compiling this, it would be appreciated. ie. the concat may not be necessary?

Re: VBA, pulling values together to do a comparison

bumpy

Re: VBA, pulling values together to do a comparison

Quote from Infomage;774260

When you say you want to compare the numbers in column A, what do you mean exactly?

You might mean that you want to indicate elsewhere whether the same or different values appear on each row, or perhaps determine whether the number on one sheet appears anywhere in the other sheet(s)...

Please clarify. The more detail the better.

Sorry.

So both files are identical set, Column A has #'s. When I say i want to compare, I mean the difference between them.
Now each number has a country and city assigned to it, B and C respectively. Also in both sheets.
Now they are not in order, so new york usa may be in row 1 in sheet A, and row 30 in sheet B, So there must be some sort of lookup? Correct?

The numbers are not identical on both workbooks, but the citys and countries are. if they arent the result should just be N/A or something.

Is that more helpful?

Re: VBA, pulling values together to do a comparison

bumpy

Hi,

my current code does

1) Opens my desired workbooks via a picker button on my master workbook (Just need to compare 2 at once)
2) Delimits by pipe
3) concatenates columns B and C (into col D)

I am stuck here, my goal is to compare column A numbers from both opened workbooks on my master sheet.

Should I vlookup my values from one workbook to another and bring in the numbers and subtract?

How can I get my values in my master sheet and do a nice little subtraction? its tough, like I looked at some code to put my sheets into master but you have to state the master sheet and the current sheets to copy (sheet names will always be dynamic names so this kind is useless)

Any help would be appreciated, Sorry i am a beginner.

Current code:

Sub Rec()
    Dim fileNames As Object, errCheck As Boolean
    Dim ws As Worksheet, wks As Worksheet, wksSummary As Worksheet
    Dim y As Range, intRow As Long, i As Integer
    
    
    




    ' Turn off screen updating and automatic calculation
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With




    'get user input for files to search
    Set fileNames = CreateObject("Scripting.Dictionary")
    errCheck = UserInput.FileDialogDictionary(fileNames)
    If errCheck Then
        Exit Sub
    End If




    For Each Key In fileNames 'loop through the dictionary




        On Error Resume Next
        Workbooks.OpenText Filename:=fileNames(Key), _
                           DataType:=xlDelimited, _
                           Other:=True, _
                           OtherChar:="|"
        On Error GoTo 0    ' or your custom error handler
        
     'delete first row
       For Each ws In ActiveWorkbook.Worksheets
ws.Range("1:1").Delete
Next ws
       
       'concat B and C and place into D
        For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(i, "D").Value = Cells(i, "C").Value & "_" & Cells(i, "B").Value
    Next i
    
   'paste into rec sheet?








    Next 'End of the fileNames loop
    Set fileNames = Nothing
















    
    ' Reset system settings
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = True
        .Visible = True
    End With
End Sub

Hi,

I would like to make my spreadsheet easier to read and just more organized.

Any links/pics or spreadsheet examples would be appreciated.

My current format goes node by node format and then columns across to represent the data.

something like this:

[TABLE="class: grid, width: 500"]

Moderators

# of posts

Active users

OZ

Excel

excel gen

x, y , z

1000

5

excel vba

a, b , c

500

10

excel formula

c , y , x

5000

15

Access

[/TABLE]

and so on, It is many nodes I have so it can be quite hard to read it all. Don't really want to pivot it as some of the column headers and data inside are quite complex.

Thanks

Re: VBA, Advanced Filter

Batman? I need help adding additional criteria.

Re: Help altering function that checks timestamp of file.

Awesome, works really good for my wildcard file paths!! How come the times differ slightly between our functions? Are yours taking the date modified? Just curious.

Re: Help altering function that checks timestamp of file.

bump

Re: Help altering function that checks timestamp of file.

bump

My current function checks a file path and returns its timestamp, ex: A1 has Oz/Grid.csv, use the function in B1 and get the time it was created.

However if my file is Oz/Grid_*.csv in A1 , the function returns a default value , im guessing because there are many files that the wildcard could apply to . So because the wildcard checks for every file with that name and usually the folder that contains the file I look for has file names such as Oz/Grid_1.csv , Oz/Grid_2.csv, etc.

How can I get this function to search for the max wildcard value and return that instead of returning a default value?

Public Function getTimestampOfFile(FilePath As String)




On Error GoTo ExitWithError




If FilePath = "" Then
    Exit Function
End If
If Dir(FilePath) <> "" Then
'This creates an instance of the MS Scripting Runtime FileSystemObject class
Set oFS = CreateObject("Scripting.FileSystemObject")




getTimestampOfFile = oFS.GetFile(FilePath).DateCreated
Else
End If




Exit Function
ExitWithError:




End Function

any help would be greatly appreciated.
Thanks

Re: Pivot, Summarize values by Maximum, 24-hour clock

Thanks, not really sure why its missing some of them, your 93 and 95 change worked well, it just went to next day, maybe the post 95 has to be reflected in 99.

think this was able to do it:

Sub FillGapsv2()
    Dim rowCount As Long
    Dim lastRow As Long
    Dim ws As Worksheet
     
     'Where are our variables?
    Const colDate As String = "A"
    Const colName As String = "B"
    Const colStage As String = "C"
    Const colStart As String = "D"
    Const colEnd As String = "E"
     
     'Which worksheet is data on?
    Set ws = ActiveSheet
     
    Application.ScreenUpdating = False
     
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         
        For rowCount = lastRow To 2 Step -1
             'Check if the same person
            If .Cells(rowCount, colName).Value = .Cells(rowCount, colName).Offset(-1).Value And _
            .Cells(rowCount, colDate).Value = .Cells(rowCount, colDate).Offset(-1).Value Then
   
                 
        




         'If start time of next segment was later than end of previous, there's Down Time
                If .Cells(rowCount, colStart).Value > .Cells(rowCount, colEnd).Offset(-1).Value Then
                     
                     'Create new row
                    .Cells(rowCount, 1).EntireRow.Insert
                    .Cells(rowCount, 1).EntireRow.FillDown
                     
                     'Fill in missing info
                     '----CHANGED THIS NEXT LINE---
                    .Cells(rowCount, colStage).Value = "Down Time"
                     '------------------------------
                    .Cells(rowCount, colStart).Value = .Cells(rowCount, colEnd).Offset(-1).Value
                    .Cells(rowCount, colEnd).Value = .Cells(rowCount, colStart).Offset(1).Value
                End If
            End If
        Next rowCount
    End With
     
    Application.ScreenUpdating = True
     
     
End Sub

Say if a downtime happened twice a day, can we name these 1 and 2 , and number them per day?

Re: Pivot, Summarize values by Maximum, 24-hour clock

I like the macro, I would like the macro to reflect your change in post #95 but be able to use it up till before the last stage on a given day. So it doesn't continue with downtime overnight till the next day.

Re: Pivot, Summarize values by Maximum, 24-hour clock

Thanks could you explain this more? what I get from it is that it only inserts down time for when stages are the same. So single stages wont have time (vegas won't have down time)

This works good but now it continues for each business day lol, aka down time after seattle is added till till the next day start.