#13

Not really. You’re looking for rows which haven’t been updated in the last three months. Presumably.

1 Like
#14

Yeah but why is it not the actual formula? Why is this a text version so you have to do it by eye? And you’d be comparing two dates not formatted as dates

#15

Covered a little bit above, but to try and clarify.

  • It’s feeding into another formula which parses the text and evaluates it against the range with the values.
  • It doesn’t need to be done like that but it can be useful to split out the components to understand how it works. To be honest, the 90 is the part I’d have put in a separate cell, then you can much more easily change to check 60 or 120 days just by modifying one cell.
  • Excel is actually pretty good at interpreting values and handling formatting like this (much more so than gSheet.) For example, if you have a column of dates entered in a standard format these two formulas will give you exactly the same result:
    =COUNTIF($L$3:$L$13,"<23/03/21") ← Date passed as a string in your local standard format
    =COUNTIF($L$3:$L$13,"<44278") ← Date passed as integer value based off whatever MS base date is.
1 Like
#16

Ha sorry, this spun out a bit.

I hadn’t seen Still here’s replyto harru when I posted (poss we posted at the same time) so my answer to Harru was just literal, as in it makes no sense because it’s devoid of context.

I trusted there was context I just didn’t know it.

This stuff about countifs sounds totally batshit though. How very very excel

#17

Think I need to brush up on my strings

In my understanding a string is any text with no numerical or formulaic value, so eg today() is different to “today()”

#18

You probably needed to do a bit of pre-reading first

1 Like
#19

That’s pretty much it and I’m probably needlessly complicating things here. The main things for this CountIf() stuff are that you need to convert your condition logic to a string before you pass it to the formula and that internally the formula will try to convert it back to a value which it can compare against each row.

Actually, another way to think of this is, how would you compare a single row?

= IF( $B2 < Today(), 1, 0)
which becomes
= IF( $B2 <25/03/21, 1, 0)

So, for a CountIf you want to pass that whole comparison logic including the < operator as text.

If you try to send that first version it’s going to look for a string which is greater in value than “today()”, which isn’t any use. Therefore, you first need to calculate the result of the Today() function and then concatenate it with the < to get a valid string

Wow. I fucking love Excel chat.

Just think of it like any other overloaded function.

2 Likes
#20

Mate.

I understand what an overloaded function is ffs😂

I meant what still here had said about it only recognising the angle bracket symbol if you’ve printed it to the cell and then reference that.

I was about to go to bed so I didn’t read your big countifs thing sorry

1 Like
#21

I think I understand it less after having read all these replies than I did to start with, and I understood it to start with

2 Likes
#22

I often wish I was better at Excel stuff but it’s generally my last line of working so I can normally get the data into a form I can easily manipulate before I get to the spreadsheet so the time I’d need to spend to learn all the more complex stuff is too great a sacrifice. These days I mostly end up using it to manipulate text data that REGEX on Notepad++ can’t resolve.

#23

I am good at it by the standards of generic office workers but there are people on here with nerdier jobs than mine who probably put me to shame

#24

In Ofsted in '99 the LEA reports were run through Access going into Excel to produce a full document. It was really some incredible coding and stuff. I honestly hadn’t realised how powerful the Office stuff could be until that point.

Equally I’ve never seen anyone attempt similar stuff since so I can only assume it’s a terrible idea (it certainly was a total nightmare to try to resolve bugs).

1 Like
#25

People who do VBA -

I want to add formulas to cells in a table, for example one of the formulas will be something like:
=days(Now(), i8)

…except instead of i8, the cell will be variable as it will be whichever row is at the bottom of the table. I can’t figure out how to do this with the range.formula code?

#26

So e.g. it would start:

sh.Range(“k6”).End(xlDown).Offset(1, 0).Formula =

But then not sure what to put after

#27

Don’t quite understand the quesiton here. You want to add formulas at the first empty row? If so, is there a master row which you can copy the formulas from?

You can use something like this to get the last row and adapt from there, but copying the .formula from somewhere else is better than messing aroudn with relative positions.

Sub CopyIt()

Dim lngNextRow as long
Dim wksUpdate as worksheet

set wksupdate = activesheet

lngNextRow = Get_Last_Used_Row(wksupdate) +1

wksUpdate.Cells(lngNextRow, 11).Formula = “=DAYS(Now(), I” & lngNextRow & “)”

set wksupdate = nothing

End Sub

Function Get_Last_Used_Row(ByRef wksScan As Worksheet) As Long

’ Purpose: Return last row containing any value. Distinct from UsedRange.
’
’ Revision History
’ 25/01/2017 The_Cosh Initial creation

Dim lngRow As Long
Dim lngCnt As Long

If Not wksScan Is Nothing Then
    
    lngRow = wksScan.UsedRange.Cells(wksScan.UsedRange.Cells.Count).Row + 1
  
    Do
        lngRow = lngRow - 1
        lngCnt = Application.WorksheetFunction.CountA(wksScan.Rows(lngRow))
    Loop Until (lngCnt > 0) Or (lngRow = 1)

    Get_Last_Used_Row = lngRow
End If ' Not wksScan Is Nothing

End Function

1 Like
#28

Basically I’ve created a sort of database, where staff use a couple of User Forms to update the workbook. The workbook itself isn’t editable bar a couple of columns.

When a new record is added a new row is created. I want a few columns to add formulas (basically, they track KPIs on our activity). At the moment I have it set up to just copy the formulas from the top record and paste them into the newly created row, but that’s kinda ugly for a couple of reasons, so want to move to just writing the formula in the code itself.

Thanks very much for the code, so with this bit:

set wksupdate = activesheet

lngNextRow = Get_Last_Used_Row(wksupdate) +1

wksUpdate.Cells(lngNextRow, 11).Formula = “=DAYS(Now(), I” & lngNextRow & “)”

set wksupdate = nothing

How does the code know which column it’s adding the formula? Is it the 11 bit? If so, should the 11 not be before the IngNextRow dimension?

#29

workSheet.Cells(row, column) refers to a single cell using numeric indexes for the row and column. In your original snippet, you were arrowing DOWN from cell K6 so I assumed it was column K where you wanted the new formula written. K = 11.

#30

Yeah, cool thanks. Weird that it’s the other way round to normal. Actually I think I knew this but it’s been a while.

#31

Excel brains, you are my only hope. I have two lists of text in Excel… one contains names and reference numbers, the other contains partial surnames. Screen shot below - I want to put a formula in column C that will check if the cells in column A contain any of the text in the cells in column E (so the formula in C2 will indicate that A2 does contain something from the list, as “Kramson” contains “Krams” etc.).

I know I’ve done this multiple times before, but my brain is broken this morning…

image

#32

This seems to do what you want