Posts by sarahb76

Re: VBA Code to put value of activeX textbox into a cell on another sheet

Ok I'm trying a different tactic...
I've added a couple of helper cells/formulas to determine the NEW cell address that I want to use for the date I've typed into my textbox.
[table="width: 500, class: grid"]

L

M

17

Name

UDF to find name of employee selected from slicer

18

Semester

Shows semester selected from a drop down (data validation list) in cell B5

19

Cell Address

Formula looks up the cell address of the name in cell M17 (=ADDRESS(MATCH(M17,Table9[LFM Name],0)+ROW(Table9[[#Headers],[LFM Name]]),COLUMN(Table9[[#Headers],[LFM Name]]),4))

20

Row #

Formula removes the column heading from the cell address in cell M19 so I'm left with the row # (=RIGHT(M19,LEN(M19)-1))

21

Number of columns to move over

Looks up the semester in cell M18 and returns the # of columns to move over based on data in another table (=VLOOKUP(M18,Table18,2,FALSE))

22

New Column

Looks up the # of columns to move over and returns what the NEW column should be based on data in another table (=VLOOKUP(M21,Table12,2,FALSE))

23

New Cell Address

Takes the new column identified in cell M22 & adds the row # from cell M20 to give us the NEW cell address for the date in my textbox (=M22&M20)

24

I wasn't sure if there would be an issue pointing to a cell address that was the value of a formula (cell M23), so I've amended my code to copy the cell address in M23 and paste the value into cell M24 so I can use that

[/table]
So NOW, my code is:

Sub enter_eval_dates()
    Dim mycelladdress As Variant
    mycelladdress = Sheets("Update Eval Dates").Range("M24").Value
    
    Range("M23").Select
    Selection.Copy
    Range("M24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("EVALS").Range(mycelladdress).Value = TextBox1.Value
    
    ActiveWorkbook.SlicerCaches("Slicer_LFM_Name").ClearManualFilter
    
    Range("M24").Select
    Selection.ClearContents
    
    ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
    
    MsgBox "Date Entered"
End Sub

But it's STILL not working! I'm BEYOND stumped on this!

I'm really stumped on this one!

I have an activeX control text box in my sheet in which the user will enter the evaluation date for the employee they chose from the slicer on the left. I want the user to be able to click the button below the textbox and have that date entered in the appropriate cell on the EVALS sheet.

In order to find the correct cell for the date to be entered in, I've added some helper formulas below my button:

Name = a UDF to enter the name of the employee selected from the slicer
Semester = the semester chosen from the data validation drop down in cell B5
Cell address = finds the cell address on the EVALS sheet of the name of the employee (which came from the UDF) in cell M17
Number of Columns to Move Over = looks up the number of columns to move over from column G in Table18 on the DATA sheet

Next I assigned the macro to my button with the following code which should, ideally, find the cell address of the employee chosen in the slicer and move the appropriate number of columns over from that cell address and enter the value of the date entered into the textbox, and then clear the slicer filter so the user can start over to enter the next evaluation date.

Sub enter_eval_dates()
    Dim mycelladdress As Variant
    Dim movecolumns As Integer
    mycelladdress = Sheets("Update Eval Dates").Range("M19").Value
    movecolumns = Sheets("Update Eval Dates").Range("M20").Value
    Sheets("EVALS").Range(mycelladdress).Offset(0, movecolumns).Value = TextBox1.Value
    
    ActiveWorkbook.SlicerCaches("Slicer_LFM_Name").ClearManualFilter
    
    ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
    
    MsgBox "Date Entered"
End Sub

Unfortunately, I can't get this to work.... I'm sure I'm missing something! If anyone can offer any help, I'd GREATLY appreciate it!!!

Here's a version of my workbook with fictitious names of course!

forum.ozgrid.com/index.php?attachment/68766/

Re: UDF Using offset to enter value in a cell if another cell containts certain text

Thanks MrRedli - unfortunately that doesn't seem to work. That's how I had the formulas originally (the 0.5 without the quotation marks) but it wasn't working, so I added the quotation marks in. As you can see from the attached workbook, it works for both Peter Dinklage (cell G4) and Maisie Williams (G10). But it does NOT work for my ADJ employees Ian McElhinney (G24), Daniel Portman (G28), and Liam Cunningham (G29).

Any ideas? (and yes, I'm still going strong with the Game of Thrones cast names in my dataset )

Re: UDF Using offset to enter value in a cell if another cell containts certain text

skywriter - WOW! I sure wish I'd seen your post BEFORE I tried to wing it on my own! But it's all for the sake of learning, right?

So I came up with almost a non-VBA approach (there's one step that required VBA, but I'll get to that) and everything was working great - until I tweaked some random other formatting elements, and suddenly my formulas have gone wonky and are no longer working properly...

I'm attaching the new and quasi-improved workbook here: forum.ozgrid.com/index.php?attachment/68274/

I added several helper columns (shaded dark grey) based on your initial suggestion, and a second worksheet with my "data" tables to reference in my formulas.

As I mentioned earlier, I have several different employee status codes and each of them gets evaluated on a different timeline depending on how many "semesters" they've been here.

The evaluation schedule is as follows:[TABLE="class: grid, width: 500, align: center"]

Status / Rank

# Semesters

When they get evaluated

FT

n/a

Every 3 years

n/a

If on the "t-track", then every semester

UA2

n/a

Every 3 years

UA1

3-5

Every 1 year

6 or more

Every 2 years

ADJ

0-3

Every semester

3 or more

Every 3 years

[/TABLE]

Now back to my helper columns and the WONKY FORMULAS that no longer seem to work properly for unknown reasons:

In column E I have a formula to look along the row and find the most recent evaluation date

In column F I have a formula to look up the status and determine how many years to add to the last evaluation date in column E (for employee that should be evaluated every semester, the result displays as 0.5, or half a year)

In column G I have a formula that adds the number of years from column F to the last evaluation date in column E to determine when the next evaluation date should be (which doesn't seem to be working for some of my 0.5 year results)

In column H I have an array formula (that I found somewhere online and tried, despite lack of proper knowledge about array formulas) that looks at the next evaluation date in column G and if that date falls between the start date and end date listed in Table8 on the DATA sheet, then it will return the associated semester. (for some reason this ALSO doesn't seem to work for everyone?)

The ONLY reason I have column I, is because of my formulas in row 3...

The whole purpose of this was to get the rest of my colorful columns populated with when my employees needed to be evaluated next. So I inserted a helper row 3, and beginning in column U (just because my data wouldn't have produced any results prior to that semester) I entered formulas in row 3 of each column to see if the "NEXT EVAL SEMESTER" value from my array formula in column H matched the table's column header, and if so, display the value "EVAL" (instead of my original "x")

The problem was that no matter what I tried, I kept getting a circular reference when trying to enter these formulas, so I'm assuming this has something to do with the nature of array formulas?

Either way, my clunky and quick solution was to insert helper column I, where I could copy the contents of column H (with my array formula) and paste the values. This is where my code came in - since the next eval semester will change once the table has been updated with new evaluation dates, I'll need to copy / paste special again - so I created a little button that will run code to do that .

Other than the array formula weirdness, everything works pretty well - EXCEPT those crazy 0.5 evals. I CANNOT figure out why they're not working!

Re: UDF Using offset to enter value in a cell if another cell containts certain text

Thanks skywriter - you're right, I should have just posted a sample workbook from the get-go!

forum.ozgrid.com/index.php?attachment/68253/

Right now, I'm just trying to tackle the solution for my employees with the rank of STATUS2 because they're the easiest - they are evaluated every 3 years. Once I figured that out, my plan was to tweak the formulas to address my employees with the ranks of STATUS1 and STATUS0 because they're evals are a little more complicated - but eventually I'm going to have to figure that out too!

As you can see from the attached spreadsheet that I inherited, we work by semesters and each semester an employee is evaluated, we manually enter the date into the correct semester column. In order to determine the next semester an employee needs to be evaluated, we have been going employee by employee and manually counting columns and entering an "x" in the correct column that the next evaluation should be. Needless to say, this is not only annoying but completely open to human error - hence why I'm working on this.

I can add as many helper columns as I need to, wherever I need to, but I still have to have each of the semester columns so we have a record of the employees' evaluation history.

I hope this all makes sense (and yes, the names are the cast of Game of Thrones :))

Re: UDF Using offset to enter value in a cell if another cell containts certain text

Shoot... Well there goes that idea...

I COULD put the formula in S1, but the whole point is that I'm trying to automate this with a formula instead of manually entering the data myself.

Let me explain better, I've got a list of staff members who need to be evaluated every three years from their start date. Right now I have a spreadsheet similar to the one below:

Name ------ Fall 2011------ Spring 2012------ Fall 2012 ------ Spring 2013 ------ Fall 2013 ------ Spring 2014 ------ Fall 2014 ------ Spring 2015 ------ Fall 2015------ Spring 2016

John Doe --- 10/15/11------------------------------------------------------------------------------------------------------- needs eval

Jane Doe------------------------------------------------------------ 3/3/13---------------------------------------------------------------------------------------------------------- needs eval

Jack Doe ---------------------2/22/12----------------------------------------------------------------------------------------------------------- needs eval

I inherited this workbook from my predecessor and what I've BEEN doing is going person by person, looking to see when they were last evaluated (where the date has been entered) and then counting the number of columns over that would represent three years later and manually typing in "needs eval."

So the problem with just entering a formula in my desired cell is that I won't always know which cell to enter that formula into - if that makes sense.

I had been playing around with adding some helper columns in between like this

| A B C D E
-------------------------------------------------------------------------------------------------
1 | Name Fall 2011 Helper Column 1 Helper Column 2 Spring 2012
-------------------------------------------------------------------------------------------------
2 | John Doe 10/15/11
--------------------------------------------------------------------------------------------------

I'd put a formula in the "Helper Column 1" something like: =IF(ISBLANK(B2),"blank","notblank")

Then I thought I could put something (my hypothesis about the UDF) in the "Helper Column 2" like: =IF(C2="notblank",OFFSET(0,17),"needs eval","")

Obviously that doesn't work . . .

I've been trying to resolve this issue with some nested if formulas, but I think I may have to use a UDF?

Here's what I want to do:

Enter a formula into cell B1 that looks at cell A1 and if the value of cell A1 = "not blank", then go over 0 rows and 17 columns from the active cell (B1) and set that cell's (S1) value to "needs eval"

I fell like this should be so easy, but I just can't figure it out!

Please forgive the cross-posting - I know it's frowned upon but I'm in dire need as I'm up against a deadline of the end of this week to figure this out! Cross-post can be found here: http://www.excelforum.com/exce…-from-it.html#post4236408

I thought this was going to be an easy problem to solve, but I'm thinking that it's apparently far more complicated than I originally thought (sigh - isn't everything?)
My worksheet is divided up into 8 main sections: RED, ORANGE, YELLOW, GREEN, BLUE, INDIGO, VIOLET, and FUCHSIA. I want the user to be able to hide and un-hide these sections at any time, so I've inserted a helper column (column N) to be able to filter as needed. I then included two macro buttons for each color section - button 1 would filter column N to remove the color from the displayed results (hide those colored rows) and button 2 would add the color back to the filter so that color would show back up in the displayed results (un-hide those colored rows).

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

COLUMN N

RED SECTION HEADER ROW

BUTTON 1 - HIDE RED SECTION

BUTTON 2 - UN-HIDE RED SECTION

RED

RED

RED

ORANGE SECTION HEADER ROW

BUTTON 1 - HIDE ORANGE SECTION

BUTTON 2 - UN-HIDE ORANGE SECTION

ORANGE

ORANGE

ORANGE

YELLOW SECTION HEADER ROW

BUTTON 1 - HIDE YELLOW SECTION

BUTTON 2 - UN-HIDE YELLOW SECTION

YELLOW

YELLOW

YELLOW

GREEN SECTION HEADER ROW

BUTTON 1 - HIDE GREEN SECTION

BUTTON 2 - UN-HIDE GREEN SECTION

GREEN

GREEN

GREEN

BLUE SECTION HEADER ROW

BUTTON 1 - HIDE BLUE SECTION

BUTTON 2 - UN-HIDE BLUE SECTION

BLUE

BLUE

BLUE

INDIGO SECTION HEADER ROW

BUTTON 1 - HIDE INDIGO SECTION

BUTTON 2 - UN-HIDE INDIGO SECTION

INDIGO

INDIGO

INDIGO

VIOLET SECTION HEADER ROW

BUTTON 1 - HIDE VIOLET SECTION

BUTTON 2 - UN-HIDE VIOLET SECTION

VIOLET

VIOLET

VIOLET

FUCHSIA SECTION HEADER ROW

BUTTON 1 - HIDE FUCHSIA SECTION

BUTTON 2 - UN-HIDE FUCHSIA SECTION

FUCHSIA

FUCHSIA

FUCHSIA

[/TABLE]

The problem is, my code doesn't save any existing filter criteria settings...
So let's say my user has already clicked button 1 for the RED section - which filters column N to exclude RED from the results, so the RED section is hidden. Now my user gets to the GREEN section and clicks button 1 to hide that section - the code should capture any existing filter criteria (in this case, RED is excluded) and modify the filter to now exclude GREEN as well) so the results would be that both the RED and GREEN sections are hidden.
Now let's say the user changes their mind and wants the GREEN section to show up again so they go to click button 2 - the code should capture the existing criteria (in this case RED and GREEN are excluded), add the GREEN back in but leave the RED out.

I've been trying to figure this out for days, and I did find the code below from this thread: http://www.mrexcel.com/forum/excel-questions/333961-capture-autofilter-state.html"]http://www.mrexcel.com/forum/excel-questions/333961-capture-autofilter-state.html[/URL] But being a VBA novice, I can't seem to make heads nor tails of it and modify it for my purposes. Of course, I'm up against a deadline of completing this project by the end of the week, so any help would be greatly appreciated!!!

Private Sub cbSplit_Click()
 Dim ws As Worksheet
 Dim filterArray()
 Dim currentFiltRange As String
 Dim col As Integer
 Application.ScreenUpdating = False
 Set ws = ActiveSheet
 'Capture AutoFilter settings
 If ws.AutoFilterMode = True Then
 With ws.AutoFilter
 currentFiltRange = .Range.Address
 With .Filters
 ReDim filterArray(1 To .Count, 1 To 3)
 For col = 1 To .Count
 With .Item(col)
 If .On Then
 filterArray(col, 1) = .Criteria1
 If .Operator Then
 filterArray(col, 2) = .Operator
 If .Operator = xlAnd Or .Operator = xlOr Then
 filterArray(col, 3) = .Criteria2
 End If
 End If
 End If
 End With
 Next col
 End With
 End With
 End If
 'Remove AutoFilter
 ws.AutoFilterMode = False
 'Your code here
 'Restore Filter settings
 If Not currentFiltRange = "" Then
 ws.Range(currentFiltRange).AutoFilter
 For col = 1 To UBound(filterArray(), 1)
 If Not IsEmpty(filterArray(col, 1)) Then
 If filterArray(col, 2) Then
 'check if Criteria2 exists and needs to be populated
 If filterArray(col, 2) = xlAnd Or filterArray(col, 2) = xlOr Then
 ws.Range(currentFiltRange).AutoFilter Field:=col, _
 Criteria1:=filterArray(col, 1), _
 Operator:=filterArray(col, 2), _
 Criteria2:=filterArray(col, 3)
 Else
 ws.Range(currentFiltRange).AutoFilter Field:=col, _
 Criteria1:=filterArray(col, 1), _
 Operator:=filterArray(col, 2)
 End If
 Else
 ws.Range(currentFiltRange).AutoFilter Field:=col, _
 Criteria1:=filterArray(col, 1)
 End If
 End If
 Next col
 End If
 Application.ScreenUpdating = True
 End Sub

Re: Choose folder, loop thru files to copy data & paste in new master workbook

Thanks, Luke! The Ron de Bruin link was excellent! I was able to modify it somewhat to my needs, but being a nube at this, I'm not sure how/where to change the code so that instead of pasting the data as values, it will paste special - paste link (I need links because users may be changing data in the original workbooks and I need to make sure that it's also changed in the master workbook.)

Also, Ron de Bruin's code creates a new workbook as the "master" workbook where it pastes everything - but I already have my "master" workbook where I want it to paste. This workbook will be open and the code will be assigned to a button for the user to click. The name of my workbook will be "MASTER BUDGET SUMMARY" and the sheet it should paste the links into will be "Sheet1". Any idea how I could change the code to paste into my existing workbook instead of creating a new one? I'm really having a hard time deciphering this one!

Here's my edited code from Ron de Bruin (not including the basic codes in used to get the file names, find last cell, etc.):

Option Explicit
'The example macro below you can use to merge a fixed range or
'all cells from one or all worksheets from each workbook in a folder
'First we call the Function "Get_File_Names" to fill a array with all file names
'There are three arguments in this Function that we can change
'With the macro below you can browse to the folder instead of enter in in the code
'2) Subfolders = True if you want to include subfolders
'3) ExtStr = file extension of the files you want to merge
'   ExtStr examples are: "*.xls" , "*.csv" , "*.xlsx"
'   "*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"
'   Do not change myReturnedFiles:=myFiles


'Then if there are files in the folder we call the macro "Get_Data"
'There are six arguments in this macro that we can change


'1) FileNameInA = True to add the path/file name in the A column
'2) PasteAsValues = True to paste as values (recommend)
'3) SourceShName = sheet name, if "" it will use the SourceShIndex and if "all" it copy from all worksheets
'4) SourceShIndex = to avoid problems with different sheet names use the index (1 is the first worksheet)
'5) SourceRng = Range you want to copy. Tip: "A:F" will copy all cells with data in this six columns
'6) StartCell = Enter the first cell and the macro will copy from that cell till the last cell on the worksheet
'   If StartCell = "" then it use the SourceRng
'   Do not change myReturnedFiles:=myFiles


'The example below will merge THE DATA FROM THE TABLE NAMED "SUMMARY" from the THIRD worksheet of each file
Sub RDB_Merge_Data_Browse()
    Dim myFiles As Variant
    Dim myCountOfFiles As Long
    Dim oApp As Object
    Dim oFolder As Variant
    Set oApp = CreateObject("Shell.Application")
    'Browse to the folder
    Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
    If Not oFolder Is Nothing Then
        myCountOfFiles = Get_File_Names( _
                         MyPath:=oFolder.Self.Path, _
                         Subfolders:=False, _
                         ExtStr:="*.xl*", _
                         myReturnedFiles:=myFiles)
        If myCountOfFiles = 0 Then
            MsgBox "No files that match the ExtStr in this folder"
            Exit Sub
        End If
        Get_Data _
            FileNameInA:=False, _
            PasteLink:=True, _
            SourceShName:="SUBMITTED BUDGET SUMMARY", _
            SourceShIndex:=3, _
            SourceRng:="SUMMARY", _
            StartCell:="", _
            myReturnedFiles:=myFiles
    End If
End Sub
' Note: You not have to change the macro below, you only
' edit and run the RDB_Merge_Data above.
Sub Get_Data(FileNameInA As Boolean, PasteLink As Boolean, SourceShName As String, _
             SourceShIndex As Integer, SourceRng As String, StartCell As String, myReturnedFiles As Variant)
    Dim SourceRcount As Long
    Dim SourceRange As Range, destrange As Range
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim rnum As Long, CalcMode As Long
    Dim SourceSh As Variant
    Dim sh As Worksheet
    Dim I As Long
    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    'Add a new workbook with one sheet named "Combine Sheet"
    ' come back later and see if I can change this to my template workbook with Set BaseWks = Activeworkbook.Activeworksheets(Sheet1)
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    BaseWks.Name = "Combine Sheet"
    'Set start row for the Data
    rnum = 1
    'Check if we use a named sheet or the index
    If SourceShName = "" Then
        SourceSh = SourceShIndex
    Else
        SourceSh = SourceShName
    End If
    'Loop through all files in the array(myFiles)
    For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
        Set mybook = Nothing
        On Error Resume Next
        Set mybook = Workbooks.Open(myReturnedFiles(I))
        On Error GoTo 0
        If Not mybook Is Nothing Then
            If LCase(SourceShName) <> "all" Then
                'Set SourceRange and check if it is a valid range
                On Error Resume Next
                If StartCell <> "" Then
                    With mybook.Sheets(SourceSh)
                        Set SourceRange = .Range(StartCell & ":" & RDB_Last(3, .Cells))
                        'Test if the row of the last cell >= then the row of the StartCell
                        If RDB_Last(1, .Cells) < .Range(StartCell).Row Then
                            Set SourceRange = Nothing
                        End If
                    End With
                Else
                    With mybook.Sheets(SourceSh)
                        Set SourceRange = Application.Intersect(.UsedRange, .Range(SourceRng))
                    End With
                End If
                If Err.Number > 0 Then
                    Err.Clear
                    Set SourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If SourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set SourceRange = Nothing
                    End If
                End If
                On Error GoTo 0
                If Not SourceRange Is Nothing Then
                    'Check if there enough rows to paste the data
                    SourceRcount = SourceRange.Rows.Count
                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet to paste"
                        mybook.Close savechanges:=False
                        BaseWks.Parent.Close savechanges:=False
                        GoTo ExitTheSub
                    End If
                    'Set the destination cell
                    If FileNameInA = True Then
                        Set destrange = BaseWks.Range("B" & rnum)
                        With SourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = myReturnedFiles(I)
                        End With
                    Else
                        Set destrange = BaseWks.Range("A" & rnum)
                    End If
                    'Copy/paste the data
                    If PasteLink = True Then
                        With SourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = SourceRange.Value
                    Else
                        SourceRange.Copy destrange
                    End If
                    rnum = rnum + SourceRcount
                End If
                'Close the workbook without saving
                mybook.Close savechanges:=False
            Else
                'Loop through all sheets in mybook
                For Each sh In mybook.Worksheets
                    'Set SourceRange and check if it is a valid range
                    On Error Resume Next
                    If StartCell <> "" Then
                        With sh
                            Set SourceRange = .Range(StartCell & ":" & RDB_Last(3, .Cells))
                            If RDB_Last(1, .Cells) < .Range(StartCell).Row Then
                                Set SourceRange = Nothing
                            End If
                        End With
                    Else
                        With sh
                            Set SourceRange = Application.Intersect(.UsedRange, .Range(SourceRng))
                        End With
                    End If
                    If Err.Number > 0 Then
                        Err.Clear
                        Set SourceRange = Nothing
                    Else
                        'if SourceRange use almost all columns then skip this file
                        If SourceRange.Columns.Count > BaseWks.Columns.Count - 2 Then
                            Set SourceRange = Nothing
                        End If
                    End If
                    On Error GoTo 0
                    If Not SourceRange Is Nothing Then
                        'Check if there enough rows to paste the data
                        SourceRcount = SourceRange.Rows.Count
                        If rnum + SourceRcount >= BaseWks.Rows.Count Then
                            MsgBox "Sorry there are not enough rows in the sheet to paste"
                            mybook.Close savechanges:=False
                            BaseWks.Parent.Close savechanges:=False
                            GoTo ExitTheSub
                        End If
                        'Set the destination cell
                        If FileNameInA = True Then
                            Set destrange = BaseWks.Range("C" & rnum)
                            With SourceRange
                                BaseWks.Cells(rnum, "A"). _
                                        Resize(.Rows.Count).Value = myReturnedFiles(I)
                                BaseWks.Cells(rnum, "B"). _
                                        Resize(.Rows.Count).Value = sh.Name
                            End With
                        Else
                            Set destrange = BaseWks.Range("A" & rnum)
                        End If
                        'Copy/paste the data
                        If PasteLink = True Then
                            With SourceRange
                                Set destrange = destrange. _
                                                Resize(.Rows.Count, .Columns.Count)
                            End With
                            destrange.Link = SourceRange.Value
                        Else
                            SourceRange.Copy destrange
                        End If
                        rnum = rnum + SourceRcount
                    End If
                Next sh
                'Close the workbook without saving
                mybook.Close savechanges:=False
            End If
        End If
        'Open the next workbook
    Next I
    'Set the column width in the new workbook
    BaseWks.Columns.AutoFit
ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
        .DisplayAlerts = False
    End With 
End Sub

I have been working on something that really has me stumped, even after searching the internet for days (although I must admit I'm still quite the novice.)

I want my code to allow the user to choose the folder (which will be a sub-folder within a network drive), then loop through all excel files in that folder and copy the data from a specific sheet (same name in each file - "SUBMITTED BUDGET SUMMARY") for each of those files and paste it into my workbook to create a master summary sheet.

Note: The copied data needs to be pasted into the MASTER SUMMARY sheet as a link, and should leave a blank line between the data pasted from each file. So copy the data from workbook1 and paste it into MASTER SUMMARY workbook, leave a blank row, then paste the data from workbook2, leave a blank row, and so on.

I've piece-mealed the code below from various searches and tested it using a folder on my desktop. It worked beautifully, but now it won't work when I try to choose the ACTUAL folder containing my files, which is a sub folder on a shared network drive.

Here's my code:

Option Explicit


Sub merge_all__input_workbooks()


    Dim wkbDest As Workbook
    Dim wksDest As Worksheet
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim MyPath As String
    Dim MyFile As String
    Dim FolderName As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Set wkbDest = ThisWorkbook
    Set wksDest = wkbDest.Worksheets("Sheet1")


    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
         On Error Resume Next
        FolderName = .SelectedItems(1)
        Err.Clear
     On Error GoTo 0
    End With


    MyPath = FolderName
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xls")
    
    Do While Len(MyFile) > 0
        Set wkbSource = Workbooks.Open(MyPath & MyFile)
        Set wksSource = wkbSource.Worksheets("SUBMITTED BUDGET SUMMARY")


'COPIES TABLE RANGE (table name = SUMMARY) FROM INPUT WORKBOOK SUBMITTED BUDGET SUMMARY PAGE AND PASTES IT TO THE END OF THE SUMMARY SHEET
        Sheets("SUBMITTED BUDGET SUMMARY").Select
        Range("SUMMARY").Copy
        Windows("MASTER BUDGET SUMMARY - ALL BUDGET OFFICERS.xlsm").Activate
            With Sheets("Sheet1")
                .Activate
                .Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Select
                .Paste Link:=True
            End With
        
        wkbSource.Close savechanges:=False
        MyFile = Dir
    Loop
    
    Call format_all_summary_page
    Call summary_pivot


    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Pivot Table"
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Budget Summary Table"
    Sheets("Pivot Table").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
    
    MsgBox "Completed...", vbInformation
    
End Sub

I've looked everywhere at various possible solutions and I can't figure it out!!! Any help would be MUCH appreciated!

Re: Conditional Formatting: blank cell if adjacent cell is not blank (cells have form

Quote from S O;736041

You're cells aren't blank, try changing to $M1=0

IT WORKED, IT WORKED, IT WORKED!!

Thanks, S O - you are officially my hero!

So my final formula was (for those looking for something similar): =AND($M1=0,$N1<>"")

Re: Conditional Formatting: blank cell if adjacent cell is not blank (cells have form

Quote from S O;736031

Try:

=AND($M1="",$N1<>"")

Thanks, SO - unfortunately that's still not working. Does it matter that the data is in a table at all? I can't figure this out and it's driving me insane!

I have been trying to figure this out for HOURS and I can't seem to get it to work - my brain is about to explode! Please help!

I'm trying to have a simple conditional formatting rule where it highlights a blank cell in column M, if the cell adjacent to it in column N is NOT blank (contains a value).

The issue I'm running into is that both columns M and N contain formulas in their cells - so a regular ISBLANK, or M1="" type of statement won't work.

Here's what I have as a formula in the conditional formatting now:

=AND(LEN($M1)<>0,LEN($N1)>0)

This works as far as highlighting any cells in column M if the adjacent cell in column N contains a value, but I want it NOT to highlight the cell in column M if that cell (in column M) also contains a value!

What am I not getting???

[ATTACH=CONFIG]63651[/ATTACH]

Re: Convert imported number &amp; text string to time

Wow - thank you ALL for the quick responses!

holycow - I didn't left align the cells, they were just imported that way.

danerida - Your formula worked perfectly! I was trying to come up with something along these lines, but I don't have the formula writing chops just yet I've been analyzing your formula to wrap my brain around it and learn from it and I have one question - on the MID statement, why do you have a +1? If I'm understanding correctly, the MID statement is saying to find the colon in L2, starting with the first character and return the 2 characters after the colon. Is that right? So I assumed that the +1 after the FIND statement meant that the MID statement ACTUALLY said: find the colon in L2 starting with the first character + one additional character and return the 2 characters after the colon. Yes? And if yes, why not just put a 2 in the find statement instead of a 1? Or does it mean something else entirely? Either way - AWESOME formula! Unfortunately I don't think I can control how the data imports. It's coming from our company database - how do I know if it's a SQL query? (please forgive me - I'm still trying to learn!)

Krishnakumar - I'm assuming you mean Ctrl+F, right? I can't believe it was that EASY! I feel like such a dunce, but of COURSE that worked perfectly! I love the formulas you and danerida provided - but I think the idea of doing a find and replace is the most simple. Ultimately I'd like to automate all of my work with a macro and I feel like it's easier to do a find and replace than it is to add helper columns, right? Or is it just a preference thing?

But thank you to ALL! This is great!

Please help! I have looked all over and CANNOT figure this out! I have imported data from a csv file. In my sheet of imported data, I have a column with times in it (column L = start time). The problem is that it imports like it's shown in column L below (it imported as a general format where it has zeros in front of every time that's not 10, 11, or 12 - and then it puts the AM/PM designation on the end) and I need it as shown in column M below.

I need the imported data to be in the time format in order for my formulas in other columns to work (the formulas are adding minutes to the start time to find the end time). There HAS to be a way to do this, right?

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

L

M

1

Start Time

Revised Start Time (how I want it to look)

2

06:00PM

18:00:00

3

08:30AM

08:30:00

4

12:30PM

12:30:00

5

07:30PM

19:30:00

5

10:30AM

10:30:00

6

11:30AM

11:30:00

[/TABLE]

Re: Multiple Occurrence Lookup Without Duplicates

Joining the thread YEARS later... and it seems like this would be the perfect thing for what I'm trying to achieve, but when I enter the code above and then enter my formula, I'm getting a name error. Any help / suggestions?

forum.ozgrid.com/index.php?attachment/55868/