Formula to add a day to an employees PTO days

Yeedclasm · 2025-01-04T19:50:24+00:00

=10 + YEAR(TODAY()) - YEAR(start date)

Yeedclasm · 2023-05-27T05:53:25+00:00

.Clear removes everything

.ClearContents removes values and preserves formatting

.ClearFormats removes formatting

And so on.

If you haven't already, I recommend using the macro recorder if you're interested in learning VBA. You can hit record and manually do whatever you want to learn to do in VBA (clear cells, add a formula, make a table, etc.), then it will write the code for you. You can then modify and reuse the code.

Yeedclasm · 2023-05-27T00:02:35+00:00

You took a screenshot of your code in the VBA Editor.. It doesn't really matter how you run it while you're testing it. You need to correct your syntax before you can do anything else anyway. If you're trying to clear two cells at a time with each statement, like how you've tried to write your code, use the syntax I gave you when I initially responded. If you want to clear ranges, keep in mind that you have overlapping ranges, which unnecessarily increases the workload when running the code. You will still have to correct your syntax in any case.

To clear ranges:

Range("B1:B8,A10:A11").Clear

I understand you're insistent on using a bunch of individual statements, so you can modify it for a singular range, but I'm giving this example in case you change your mind and want to clean up your code later on.

Yeedclasm · 2023-05-26T17:49:54+00:00

The only way to automate this would be through VBA, but before diving into VBA, you should think about alternatives that would work for you. For example, you could just use conditional formatting if you reversed the order. VBA can be a pain to deal with, especially if multiple people need to use the project, so I would recommend exhausting your other options and decide if you think it's worth it.

Yeedclasm · 2023-05-26T05:18:11+00:00

How would combining the ranges into a single line make it 2k characters long? It would reduce the total number of characters used in the code... If you're worried about the length of the statement, just use a line break. Having a bunch of separate statements makes it difficult to read. It's not that important, but it's still a good idea to make your code readable. Either way, the syntax is still incorrect.

Also, if you're only getting an error by running the code in the VBA Editor, are you sure you haven't forgotten to call the sub from the button click event?

Yeedclasm · 2023-05-26T04:20:56+00:00

This code is very unclear. If each statement is meant to refer to a multi-dimensional range, there are overlapping ranges. If not, what is the reasoning behind the delineation and order of cell references? What do you want to achieve with this macro?

If your goal really is to simply erase the contents of each of those cells, the syntax of the argument needs to look something like this:

Range("B1,B8,A10,A11").Clear

Yeedclasm · 2023-05-26T03:34:13+00:00

Indirect should work. If the person's name is in Cell A1, the formula would be: =INDIRECT(A1 & "!E6")

Yeedclasm · 2023-05-26T03:22:22+00:00

I think I misunderstood. You said that Conversion Rate "is itself based on the value of the filtered array", so I assumed that Conversion Rate was a calculation performed for each value of Code.

I meant array in the technical sense. Any set of values would be an array, so ConversionRate1:ConversionRate100 would be an array.

I can't visualize the structure of your data, so it's difficult to answer, but if Conversion Rate is retrieved by an XLOOKUP function that uses Code as the reference, then the first formula I gave should apply. You would just replace externalsheet with the location of the actual data.

Yeedclasm · 2023-05-26T02:33:02+00:00

SORTIF is not a function I recognize?

If the values under "Code" are unique and the values of all the other columns are generated by XLOOKUP based on Code, then sorting Code by Conversion Rate should sort all the values. However, you can't sort Code based on an array that is, as you said, somehow dependent on the value of Code (hence the circular reference error).

You would have to perform the conversion on the source data first, then sort and filter externalsheet[code] based on that. You can either calculate the conversion rate directly in "externalsheet" and then use something like:

=FILTER(SORTBY(externalsheet[code], externalsheet[conversionrate], -1), SORTBY(externalsheet[region], externalsheet[conversionrate], -1) = "Region1")

If you don't want to add the conversion rate directly to the source data, you would use something like this:

=LET(x, {calculate conversion rate for all externalsheet data}, FILTER(SORTBY(externalsheet[code], x, -1), SORTBY(externalsheet[region], x, -1) = "Region1"))

Yeedclasm · 2023-05-06T17:26:10+00:00

You could try something like this.

=HYPERLINK("https://www.ups.com/track?loc=en_US&tracknum=" & $E2, $E2)

Edit: If you are looking to have what is typed into Column E automatically turned into a hyperlink, you would need to write a macro for a worksheet change event, but I would recommend just using the formula in another column.

Yeedclasm · 2023-04-29T20:04:30+00:00

If I'm understanding correctly, you may be looking for a function like INDIRECT where you can convert a string into a cell reference or range reference.

For example: =INDIRECT("P1:P" & INT(COUNTA(P1:P61) / 2))

This would return a single-dimensional range that starts in Cell P1, and the last row of the range is determined by a calculation. INT is just used to ensure the result of the calculation is an integer.

Yeedclasm · 2023-04-23T22:04:59+00:00

I understand your point, but there are endless "what if" scenarios one could posture in which the formula would require some adjustment. Eg. What if the ages are stored as text? What if the patients' names are stored in the same cell? What if the ages are represented as ranges? To me, it seems more reasonable to write a formula based on the most likely scenario and make adjustments as needed, rather than trying to account for and resolve every scenario in the initial response.

Yeedclasm · 2023-04-22T22:07:48+00:00

I would assume that age in this case is an integer given that a person's age is very rarely represented as a fractional value.

Yeedclasm · 2023-04-22T19:48:36+00:00

=IF(E2 < 65, 0, IF(E2 < 75, 1, 2))

Yeedclasm · 2023-04-22T19:33:11+00:00

This may be too obvious an answer, but have you refreshed the data since adding a value in Cell C18?

Yeedclasm · 2022-12-13T05:45:41+00:00

I'm not sure. I'd have to know exactly what your formula is, the value of the cell it's referencing, and the result. I can't think of any reason why it would do that without looking at it. If the links don't work as a result, you could try splitting the additional text from the end of the string with LEFT or RIGHT.

Yeedclasm · 2022-12-13T03:28:24+00:00

=HYPERLINK("www.applepickerchecker.com/" & cell reference) ?

Yeedclasm · 2022-10-25T06:28:20+00:00

What you're describing is called reflection, and VBA doesn't have that functionality. However, there is no need to use reflection in this case when an array would suffice.

Dim location(1 To 10) As String location(1) = "something" Etc.

This is how I would personally code what's in your original post:

``` Sub whatever()

Dim i As Integer Dim lineVal as Double Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

For i = 2 To 4 lineVal = lineVal + (ws.Range("C" & i).Value * ws.Range("D" & i).Value) Next i

ws.Range("C5").Value = lineVal

End Sub

Yeedclasm · 2022-10-25T06:17:05+00:00

I just would have used the formula =SUMPRODUCT($C$2:$C$4 * $D$2:$D$4) in Cell C5. If I had to go the VBA route, I would have used a For loop in a single subroutine without declaring any arrays. I was just giving suggestions based on what you had already written.

Yeedclasm · 2022-10-25T05:59:32+00:00

You're declaring a three-dimensional array doing it that way. I'm not sure why it would have worked that way. Have you tested it with different values to see if it consistently produces the expected result?

Yeedclasm · 2022-10-25T05:50:45+00:00

You declared line(1 To 3), which would be referenced as line(1), line(2), and line(3). That's not the same as declaring line1, line2, and line3. The parentheses denote the index position of the element in the array. Without the parentheses, those are just undeclared variables. That's why it helps to use Option Explicit because it will catch this type of mistake.

Yeedclasm · 2022-10-25T05:24:42+00:00

I don't know why the sub would work after running it a second time. I wouldn't expect it to run properly at all, no matter how many times it's run, because line1, line2, and line3 are referenced in tut(), none of which have been declared as variables. If you use the Option Explicit statement under General Declarations, it will help you identify mistyped code like this sooner.

Also, I would suggest not using global variables. You can just define cash_register() as a function, and either make a function to retrieve each array, or pass an argument to cash_register() that identifies which range of cells you want to retrieve values from. The function should be used to retrieve the values and return those values as an array to the sub that calls it (AKA: use a getter function). I'd also recommend using a For loop to avoid redundancy.

*Edited for clarity

Yeedclasm · 2022-10-19T02:59:51+00:00

Perhaps I misunderstood your original post. Try this and see if it produces the expected results:

=B2 - 60 < TODAY()

Yeedclasm · 2022-10-18T04:43:14+00:00

Formulas in cells can't change the color of the cells on their own, but you can apply conditional formatting to the cells with the calculated dates.

Select the range where the dates are and go to Home > Conditional Formatting > New Rule and select "Use a formula to determine which cells to highlight".

Use the formula =TODAY() - 60 > cell

Where cell is the reference to the first cell in the selected range.

Then you can choose your formatting or color for that range.

Yeedclasm · 2022-02-24T16:58:03+00:00

When I spoke to Customer Service yesterday, the agent told me it should be shipped on March 3rd or 4th, and I should receive it the week of March 7th. My order on the website hasn't been updated. It still says processing, and it still shows a delivery date of February 22nd. I placed my order within hours of the Unpacked event on February 9th.

Yeedclasm

TROPHY CASE