Posts by BScott05

Hello,

I am creating a named dynamic range for a chart to dynamically update as values are automatically populated based on a date.
I am using the following formula:

=OFFSET('Chart Data'!$B$1,1,0,COUNTA('Chart Data'!$B:$B)-1,1)

this is my data as of now:
[TABLE="width: 500"]

[TABLE="width: 287"]

Date

Audits (Cumulative)

6/1

[TD="align: right"]158

6/2

[TD="align: right"]381[/TD]

6/3

[TD="align: right"]554[/TD]

6/4

[TD="align: right"]554[/TD]

6/5

[TD="align: right"]554[/TD]

6/6

[TD="align: right"]730[/TD]

6/7

[TD="align: right"]761[/TD]

6/8

[TD="align: right"]761[/TD]

6/9

[TD="align: right"]761[/TD]

6/10

6/11

6/12

6/13

6/14

6/15

6/16

6/17

6/18

6/19

6/20

6/21

6/22

6/23

6/24

6/25

6/26

6/27

6/28

6/29

6/30

[/TABLE]
[/TD]

[/TABLE]

the blank cells in column B equal "". I want to omit the "" cells in the count A for my heighth in the above formula

let me know if I need any further clarification on this.

thank you for the help!

Hello,

I have a document that has a specific Date in cell A1. the date is dynamic based on other variables but are not needed for this example. I also have Dates in B1: ?? based on the month in A1 (i.e A1= 6/8/2014, B1= 6/1/2014 C1=6/2/2014 -> 6/30/2014).

I am wanting to create a formula that will count the column that matches cell A1 with al the matches to the value in cell A2 (A2= X).

Please let me know if i need to clarify my question. Im guessing i will have to do some sort of countifs and match combination, but cant quite figure it out.

Thank you!

Re: Formula to count cells with specific characters using sumproduct & Subtotal

ok, i repasted the formula into my doc and it is working all of a sudden. Not sure what i did wrong before but it is working perfectly!!!
Thank you very much!

so the "FIND" or "SEARCH" function will search for specific characters within the range specified?

that seems like a more handy and celaner way to run these type of searches...

Re: Formula to count cells with specific characters using sumproduct & Subtotal

Thank you! I was not aware the wild card wouldnt work in that type of formula. I plugged in your formula above but it is giving me a boolean return (true/ false). the returned results are all false, when i do in fact have WL1 WL2 etc in the range.

Any idea why?

Hello,

I have a document that has several filters attached to its headers. I want to create a formula that will basically count the VIEWABLE cells that meet the criteria.

here is the formula that i am having trouble with:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(J29,ROW(J28:J368)-ROW(J29),0)),(J28:J368="*"&"WL"&"*")+0)

Basically what i want to do with this is count any cells that have the characters WL in it and display a total based on the viewing filter. Some of these cells have a WL2 or WL3 etc, i want to count as many WL's that are viewable.

I have "*"&"WL"&"*" in place for a wildcard for any WL's. (this works in a regular count if formula).

Any idea why i am not able to calculate them?

Re: Create a userform that finds a cell address based on 2 input boxes

Quote from fredlo2010;696267

Hello,

Maybe something like this will help

Code

Function InputBoxSolution()
     
    Dim lRow As String
    Dim lcol As String
     
ColumnEntry:
     ' First input box
    lcol = TextBox1("Enter the Column Letter", "Column Input")
     ' Check thats a letter 3 in legth, a letter and not empty
    If Len(lcol) > 3 Or Not lcol Like "[a-zA-Z]" Or lcol = vbNullString Then GoTo ColumnEntry
     
RowEntry:
     ' First input box
    lRow = TextBox2("Enter the Column Letter", "Column Input")
    If lRow = vbNullString Then GoTo RowEntry
     
     ' Select the cell
    Range(lcol & lRow).Select
     
End Function

Display More

Thanks

Display More

Thank you for the input fredl02010, i am getting an error saying:
Compile error: Sub Or function not defined at line:
Icol = textbox1

Also, does this function search a specific column? I have a user ID in the column, and Dates in the row. So, textbox1 would have a user ID and textbox2 would have a date.

Hello,

I am trying to create a Userform that would search a sheet based on the 2 inputboxes. I would like to search a specific column for one input box, then the column for the second inputbox. Where the 2 coordinates intersect, i would like to select that cell and input a value based on a 3rd selection. I am pretty new to VBA, but im sure this is a fairly simple task to complete.

any help on this would be much appreciated!

EX:
[TABLE="width: 240"]

[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]

[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$1.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$4.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$7.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$10.00[/TD]

[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$2.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$5.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$8.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$11.00[/TD]

[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$3.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$6.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$9.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$12.00[/TD]

[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$4.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$7.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$10.00[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]$13.00[/TD]

[/TABLE]

I'd like to input "B" into 1 input box and "2" into the second input box and have the userform select the "$5.00"cell.

Hello,

I have created a piece of code in a userform that will search my specific worksheet and return all the results based on one combobox(cboFunction). The problem i am having is that it is only returning the data that is populated in Column A. I would like the listbox to return all the data in the row based on the search criteria (matching cboFunction's value). Is there something i am missing?

I have pieced this code togetehr from bits and pieces of other searches i have made int he past. But i have been stuck on this issue for the past 4 days and cannot wrap my head around it. Im not particularly fluent in VBA ( i have learned by piecing bits and pieces together over the years).

Any help would be appreciated!!
CODE:

Private Sub CommandButton01_Click()
Dim rngNames As Range
Dim arrNames
Dim arrResults
Dim lngRow As Long
    If cboFunction.Value = "" Then
        ListBox1.Clear
        Exit Sub
    End If
    
    With Sheets("Strategy Raw")
        Set rngNames = .Range("A5", .Range("A" & Rows.Count).End(xlUp))
    End With
    With rngNames
        arrNames = Evaluate(.Address & "&CHAR(45)&ROW(" & .Address & ")")
    End With
    arrNames = Application.Transpose(arrNames)
    arrResults = Filter(arrNames, cboFunction.Value)
    ListBox1.Clear
    If UBound(arrResults) = -1 Then
        ListBox1.AddItem "No matches"
    Else
        For i = LBound(arrResults) To UBound(arrResults)
            lngRow = Mid(arrResults(i), InStrRev(arrResults(i), "-") + 1)
            With ListBox1
                .AddItem
                .List(.ListCount - 1, 0) = Sheets("Strategy Raw").Range("A" & lngRow)
                .List(.ListCount - 1, 1) = Sheets("Strategy Raw").Range("B" & lngRow)
                .List(.ListCount - 1, 2) = Sheets("Strategy Raw").Range("C" & lngRow)
            End With
        Next i
    End If
End Sub

I cannot upload my document due to work restrictions, but there is currently 10 columns of data for each row of data.

Hello,

I have a spreadsheet that contains employee break times and lunch times. I want to highlight the cells that should be on a break or lunch based on the current time of day. I honestly do not know where to start for afromula that will calculate this. The cells need to be highlighted for their duration of their specific time.. (i.e break is 20 minutes, lunch is 35 minutes).

Is there anyone that could possibly point me in the right direction?

My data starts from colums F to M

Re: HLookup Index number Auto Decrease

Yea, each row_index_num needs to reference to row 77. As I drag the formula down, i need to manually decrease the Row_Index_num by 1 for each row, in order to reference back to row 77

Re: HLookup Index number Auto Decrease

Thank you Cytop for the reply.

So, the references for the lookup Value should be constant. I want to use E5 as the Lookup Value for each row and also have K7:CJ77 as the table array for each Hlookup in each row. I only need the "row_index_num" to decrease by 1 as i drag the formula down through each row.

Hello,

I have a document with 150+ rows of data. The data has a few HLookups in each row that will look for a specific value and then reference to a specific time interval. I can get the first row of forumlas to return the correct values, but when i drag the formula down the document, the Row_Index_Num does not change. I am having to manually change the Index # to reflect the data i am wanting to return.

Is there anyway to have the row index # change automatically? The row index # i want every formula to reference is in 1 single row (row 77), itdoes not move or change

here is what i have

Cell E7: =HLOOKUP(E$5,$K7:$CJ$77,71,FALSE)
Cell E8: =HLOOKUP(E$5,$K8:$CJ$77,70,FALSE)
Cell E9: =HLOOKUP(E$5,$K8:$CJ$77,69,FALSE)

Can anyone help with this?

Thank you!

Re: Create Outlook Appointments based on cell info

Thank you for the help!

so i tried plugging the code in above to fit my question.. and it basically loops constantly and wotn stop creating appointments..

Sub TEST_APPT()


    Dim objOL       As Object
    Dim objItem     As Object
    Dim tzCentral   As Object
    Dim cell As Range
    
    Set objOL = GetObject(, "Outlook.Application")
    Set tzCentral = objOL.TimeZones.Item("Central Standard Time")
    Set objItem = objOL.CreateItem(1)
    
    
    For Each cell In ActiveSheet.UsedRange
    If cell.Interior.ColorIndex = 6 Then 'yellow
        Set objItem = objOL.CreateItem(1)
            With objItem
             .Start = (Date) + ActiveSheet.Range("A8").Value
             .StartTimeZone = tzCentral
             .Duration = 0
             .Body = "Strategy Document link: [URL="http://team.usaa.com/sites/bank/cstratgy/Run%20Library/MDS%20Strategy%20Document.xlsm"]http://team.usaa.com/sites/bank/cstratgy/Run%20Library/MDS%20Strategy%20Document.xlsm[/URL]"
             .BusyStatus = olFree
             .AllDayEvent = False
             .Subject = ActiveSheet.Range("B8").Value
             .ReminderMinutesBeforeStart = 10
             .ReminderSet = True
             .Display
            End With
    End If
    Next
        
    For Each cell In ActiveSheet.UsedRange
    If cell.Interior.ColorIndex = 6 Then 'yellow
        Set objItem = objOL.CreateItem(1)
            With objItem
             .Start = (Date) + ActiveSheet.Range("A8").Value
             .StartTimeZone = tzCentral
             .Duration = 0
             .Body = "Strategy Document link: [URL]http://team.usaa.com/sites/bank/cstratgy/Run%20Library/MDS%20Strategy%20Document.xlsm[/URL]"
             .BusyStatus = olFree
             .AllDayEvent = False
             .Subject = ActiveSheet.Range("B8").Value
             .ReminderMinutesBeforeStart = 10
             .ReminderSet = True
             .Display
            End With
    End If
    Next
        
        MsgBox "Your strategy reminders have been created for " & Sheets("Admin Menu").Range("H4"), vbOKOnly, "Strategy Reminder"
    
    
    Set objItem = Nothing
    Set objOL = Nothing
    Set tzEastern = Nothing
    
End Sub

Maybe this would be easier to do.. I want to select all the cells that are yellow (color 6 on the index) and create an appointment for each cell that is selected and set the time based on the cell exactly 1 cell to the left of the selected cell...Would that be easier to do?

Re: Create multiple appointments from active sheet

Not sure if i should create a new thread or if i can ask in here because it is based ont he same code above.. but is there a way for the code to not create the appointments if the appointment times have already passed?

Hello,

I was wanting to create appointments in outlook based on cell's that have a yellow background. Next to each of these cells is a time that these appointments have to be created. I currently have this setup, but the specific cells are hard coded into the macro. This is a very tedious process to setup if i am editing the document and have to move cells around etc..

Is there anyway to create a macro that will look at the activesheet and create the appointments based off the yellow cells and then set the time value that is exactly left of the cell?

here is what i am currently doing...

Sub FE_WEDNESDAY()
    Dim objOL       As Object
    Dim objItem     As Object
    Dim tzCentral   As Object
    
    Set objOL = GetObject(, "Outlook.Application")
    Set tzCentral = objOL.TimeZones.Item("Central Standard Time")
    Set objItem = objOL.CreateItem(1)
    
    intresponse = MsgBox("Do you want to setup " & ActiveSheet.Range("B6") & " reminders?", vbYesNo, "Custom Reminder")
    If intresponse = vbYes Then
   
    '7AM
    Set objItem = objOL.CreateItem(1)
    With objItem
        .Start = (Date) + ActiveSheet.Range("A8").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B8").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With


    MsgBox "Your strategy reminders have been created for " & Sheets("Admin Menu").Range("H4"), vbOKOnly, "Strategy Reminder"
    
    ElseIf intresponse = vbNo Then
Exit Sub
    
    Set objItem = Nothing
    Set objOL = Nothing
    Set tzEastern = Nothing
    
    End If
End Sub

If i need to elaborate or say further detail, i am more than happy too..

Thank you for the help!

Re: Create multiple appointments from active sheet

wow....
THANK YOU CYTOP.. i cant believe it was that simple. I have been staring at this thing fr the past day and clearly was going cross-eyed.

Thank you

hello,

I am trying to create multiple appointment items in outlook based on the activesheet in excel. I created this same format when creating task items in outlook and all tasks were created. When i run this code, only the last object is created as an appointment in outlook. Is there soemthign i am missing?

Sub FE_WEDNESDAY()
    Dim objOL       As Object
    Dim objItem     As Object
    Dim tzCentral   As Object
    
    Set objOL = GetObject(, "Outlook.Application")
    Set tzCentral = objOL.TimeZones.Item("Central Standard Time")
    Set objItem = objOL.CreateItem(1)
    
    intresponse = MsgBox("Do you want to setup " & ActiveSheet.Range("B6") & " reminders?", vbYesNo, "Custom Reminder")
    If intresponse = vbYes Then
   
    '7AM
    With objItem
        .Start = (Date) + ActiveSheet.Range("A8").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B8").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
        
    '10AM
    With objItem
        .Start = Date + ActiveSheet.Range("A24").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B24").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
    
    '1030AM
    With objItem
        .Start = Date + ActiveSheet.Range("A40").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B40").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
    
    '11AM
    With objItem
        .Start = Date + ActiveSheet.Range("A45").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B45").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
    
    '4PM
    With objItem
        .Start = Date + ActiveSheet.Range("A52").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B52").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
    
     '4PM
    With objItem
        .Start = Date + ActiveSheet.Range("A67").Value
        .StartTimeZone = tzCentral
        .Duration = 0
        .BusyStatus = olFree
        .AllDayEvent = False
        .Subject = ActiveSheet.Range("B67").Value
        .ReminderMinutesBeforeStart = 10
        .ReminderSet = True
        .Save
    End With
    MsgBox "Your strategy reminders have been created for " & Sheets("Admin Menu").Range("H4"), vbOKOnly, "Strategy Reminder"
    
    ElseIf intresponse = vbNo Then
Exit Sub
    
    Set objItem = Nothing
    Set objOL = Nothing
    Set tzEastern = Nothing
    
    End If
End Sub

Am i missing something in between the objects?

Thank you for the help! This is my first post... I always reference to this website for my questions and usually there is an answer to be found.. but i couldnt find anything exact so i joined and am excited to be involved in these forums.