Posts by Shezmo1

    Hello. i'm wondering if someone could help with a VBA issue i'm having with a userform. i admit i'm not the best at Excel and while i have been able to achieve half of what i need i'm struggling with the other half.


    basically i have a userform that i want to use to update data on another sheet. So far i have been able to get into a position where i can use a combox box to select a user and then have the relevant textboxes on the form populate with information from several columns; but the thing i am struggling with is then being able to make changes and then have this written back to the relevant columns for the selected user. i have searched on google for similar queries but due to my lack of understanding of VBA i havnt been able to modify any of them to suit my needs. i'm also not sure if i have gone down a particular path that i shouldnt have gone down


    i have attached a sample sheet i f that makes it easier but i would appreciate any help

    Hello. i hope someone is able to help me but i am having some issues creating some IF formulas that need to return one of three results, the words, Green, Amber, or Red in the cell in column BA based on the criteria below.


    Input Green = If the value in Q5 is above the value in Cell L10 on the "Data" sheet

    Input Amber = If the value in Q5 is within 0.5 of the value in Cell L10 on the"Data" sheet

    Input Red = If the value in Q5 is 0.51 and below of the value in Cell L10 on the "Data" sheet


    The first two i have managed to do using the following formulas:


    =IF(Q5>'Data'!L10,"Green", "")

    =IF(ABS(Q5-'Data'!$L$10)<=0.5,"Amber", "")


    they seem to work fine based on my testing however, and i may be missing something stupidly obvious, when i try to do the formula for the third, 'Red' criteria it doesnt, work the formula i was using is:


    =IF(ABS(Q5-'Data'!$L$10)>=0.51,"Red","")


    i was probably naive thinking i could simply switch the comparator around and change the number


    Would someone be able to help with how the formula should be written? and if possible, help me nest them into one IF statement please.

    I wondered if someone might be able to help me with an issue I am having when applying conditional formatting based on the value of another cell



    I have a spreadsheet and I am trying to use conditional formatting to highlight the cells depending on how they compare to a value in a cell on another sheet. The problem I am having is when I try to apply a rule to highlight cells in column Q of my ‘Report sheet’ it automatically highlights the cells even if they are blank. (not sure if this is because the cell it references contains a formula)


    The setup of my spreadsheet is as follows.:


    I have two sheets ‘Report’ and ‘Data’ on the report sheet. In column Q I have a rule that states


    Highlight if cell value is greater than =Data!$L$10 this then applies the formatting to the whole column. I think this has something to do with the formula which is in cell L10 which is ‘=L7/3’ I have tried playing around with using IFERROR in cell L10 but still get the same outcome. I have attached a sample sheet, I realise this is probably something very simple I am overlooking but I’m not sure what



    Thank you in advance for any help you can provide

    Really sorry for asking this and I hope its in the right place as its to do with formula’s but within conditional formatting, but i need some help with conditional formatting in Excel. i have a sheet and in column A i have conditional formatting to highlight any cell that is less than 10. However I need to add an exception into this where if the value in Column D was less than 2 hours (formatted as 02:00:00) then even if the value in column A was less than ten the rule wouldn’t be applied

    I did have the formula =AND($A5<10,MROUND($D5,"1:00")<TIME(2,0,0)) which worked initially but after closing and reopening the sheet no longer appears to be working when I paste in my new data I’m not 100% sure if this is due to the order in which the rules are applied but no matter which way round they are and which way the ‘stop if true’ boxes are ticked it works once but then when trying again stops. I have checked and the range to apply the rule to and formula have not changed.

    Would anybody be able to help with either why my formula stops working or a more efficient way of doing what I need.

    My data starts in A5 and D5 and goes down to A58 and D58

    Any help would be greatly appreciated

    So i think i have been a massive idiot, the main reason i wanted this was to copy the filtered results based on two criteria to the next empty row on another sheet, but i already had a macro that did that so i recorded a macro of myself applying the filter and pasting the data onto the other sheet THEN applying my existing macro which puts the data into the next available cell. so this issue is now resolved and i am sorry to have wasted time.


    Stay safe!

    Hi thank you for your reply.


    i tried recording a macro that would apply the filter i wanted to my data but when it came to copying it to the new sheet when copying it reference the first cell from the filter not from A2 which is where the filtered data would start from (sorry i know thats confusing)


    i have attached a new workbook with an example. On the 'Instances' Sheet you can see where it has filtered the initial results based on the term 'Missed' in column D (Which i have the macro for) but then i want to run another filter on the same data for any instances of the term 'Busy' in Column d AND where the Duration is above 00:04:00 in column I, and if those criteria are met put the value of A and D from the same row on the next empty row of the Instances sheet'

    Hello


    i wonder if anybody can help me. i am putting together a sheet that will hold a large amount of raw data. from that data i would like to filter results and copy certain cell references to the next empty row on another sheet.


    I have been able to put together some code that will search down column D on my ‘Raw Data’ Sheet and if the word Missed is found it will copy Cells A and D onto another sheet – ‘Instances, starting in cells A2 and B2 and will copy into the next empty row.


    What I would like to do is either modify what I have or even re-write and have two separate macros. So that it in addition to that search it also searches column D for the word ‘Busy’ and any instance in Column I where the duration is above 00:04:00 and then copy those results to the next empty row on my ‘Instances Sheet’ I have attached a sample Workbook.


    I have also attached my code but I am not sure on how to modify what I have to search for two values in two separate columns


    Thank you in advance for any help you can provide.


    Hello! I’m wondering if someone could help me with some VBA. What I could like is something that if a cell in column D starts with a particular string then a certain word is entered in the cell immediately left to it in Column C is entered. So for Example. If Cell D3 starts with Testp/ then ‘Check’ is entered into cell C3. The data range is not constant so sometimes there may be 10 rows, sometimes. Thanks in advance

    I am trying to put a task rota together; This includes Daily and weekly tasks (those only done once a week)


    Rather than have to manually update who is assigned on a weekly basis I put together a table which basically performs a VLOOKUP and looks at the Thursday of each week, I have the formula:


    =VLOOKUP($F$1+(3-WEEKDAY($F$1,2)+1),$B$56:$J$146,2)


    Where the function =TODAY() is in cell F1, Whilst this works, my question is, is there a way of replacing the F1 reference with the TODAY function in the formula directly?


    I tried just changing it which didn’t work and I appreciate I’m probably using a convoluted way of doing something very simple.


    Thanks in advance for any help.

    Hello. i have some VBA that basically works through a series of worksheets looking for 'events' that happened in the last 28 days from the current data, once found it copies them to a new sheet called 'Monthly'. The names of the sheets to work through is taken from a 'Overview' Sheet that has a list of names in Column C. the code used to work however now it goes through the motions and opens the monthly sheet but no data is copied there despite there being no errors or option to debug, below is the code i have would anyone be able to tell me why this no longer works, apologies if i am missing something really obvious.


    [VBA]Sub Monthly_Review()


    Application.ScreenUpdating = False


    On Error Resume Next


    Dim BlankRow As Long
    Dim StartDate As Long, EndDate As Long

    StartDate = Date - 28
    EndDate = Date
    Worksheets("Monthly").Visible = True


    R = 5
    Do Until Sheets("Overview").Range("C" & R).Value = Empty
    Agent_name = Sheets("Overview").Range("C" & R).Value


    Worksheets(Agent_name).Visible = True
    Sheets(Agent_name).Select
    ThisWorkbook.Worksheets(Agent_name).ListObjects(Agent_name).Range.AutoFilter Field:=3, _
    Criteria1:=">=" & StartDate, _
    Operator:=xlAnd, _
    Criteria2:="<=" & EndDate
    Range(Agent_name).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Monthly").Select
    BlankRow = Range("A65536").End(xlUp).Row + 1
    Cells(BlankRow, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets(Agent_name).Select
    ActiveSheet.ListObjects(Agent_name).Range.AutoFilter Field:=3
    Sheets(Agent_name).Visible = False


    R = R + 1
    Loop

    Sheets("Monthly").Select


    'Dim c As Range
    'Dim SrchRng

    'Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    'Do
    'Set c = SrchRng.Find("0", LookIn:=xlValues)
    'If Not c Is Nothing Then c.EntireRow.Delete
    'Loop While Not c Is Nothing

    Application.ScreenUpdating = True

    End Sub[/VBA]

    Hello


    i am using an IF statement to compare the Values of two cells and if they match performing a Vlookup.


    Part of the reason i am doing this is because i am running a report on a weekly basis but want to keep a running total of hoe many times a person does a particular thing.


    the columns have dates at the top and the IF statement compares a call on another sheet with a dat that corresponds to the date at the top of one of the columns, if it matches it does a Vlookup to input the count of how many of X a person has done that week.


    The problem i have is that whilst this works. as soon as the date in the Cell changes the IF statement fails and the data disappears is there anyway of keeping it?

    Hello.

    I am looking for a way to do a Vlookup and insert data into a column based on another column value.


    I will be running some data on a weekly basis. And rather than manually type the numbers in would like a way of it being automated to some extent.


    On the sample sheet attached I would like a formula to go in D2 that will perform a VLookup for the name of the person in A2 in the range A18:B30 and input the number accordingly.


    What I would also like is to input the date for the week in question in cell J3 and have the formula also look for the date in there and update the columns accordingly.


    This is so I can run the data each week and by changing one cell the formula will know which column to update with data


    Not sure if that is best achieved with a formula or VBA I did try to put something together using VLookup and Match but have not been able to get anything working

    Thank you in advance for any help or advice you can offer.

    Hello i have a spreadsheet - copy attached and on 'sheet1' there is a button that says 'Add agent' this then launches a userform with a name and site box to add the person into the list. for some reason i keep getting the error Error - Method 'Range' of 'object' Global Failed when trying to add an agent this has worked previously and i actually took the code from another sheet that did the same thing that still works, i have checked the code and cannot see any obvious reason why it now won't add anything into that list and would be grateful if someone would be able to take a look and possibly shove me in the right direction to fixing it.


    thanks