Excel Help Thread 📊

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

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.

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

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

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?

So e.g. it would start:

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

But then not sure what to put after

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

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?

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.

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.

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

This seems to do what you want

Thanks. I don’t think the examples there will work for what I’m after/my brain is possibly just too mushy today… I think I’m going to tell my colleague what they’re after (this is just part of it) is too complicated and see if they’re happy with a rough estimate :slight_smile: .

I can do it in a fairly convoluted way in google sheets if that’s any use but i couldn’t work it out in excel

Cheers. I think the below page has pretty much what I was going for, but I was going wrong somewhere. Presented colleague with a rough way of getting to what they wanted and they seem happy!

https://www.get-digital-help.com/if-cell-contains-text-from-list/

Ah glad you got there. I was going for something involving an array but I’m not very good at arrays in excel because i only really use google sheets!

1 Like

I was just about to post something about Excel!

Nowhere near as complicated as the above, but: in all my years of using Excel, I’ve never been able to remember the right way to freeze panes on a spreadsheet. I end up managing, but through trial and error rather than anything else.

2 Likes

anyone any good with TEXTJOIN?

What are you trying to do with it?

I’m using textjoin to display a list of where a value is repeated. Something simple like textjoin(if(value=array)display text) however I’d like the display text to ignore the first value and just show the other repeated ones. I’m too thick to work it out :frowning: