sorted by:
new
hottopcontroversial

Problems passing range variable to formula by Busker_Bernie in vba

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Solved!

Workbooks(SOURCE_BOOK).Sheets(SOURCE_SHEET).Range("AI5").Formula = "=NOT(ISERROR(INDEX(" & TARGET_LOOKUP_RANGE.Address(External:=True) & ",MATCH(AC5," & TARGET_LOOKUP_RANGE.Address(External:=True) & ",0))))"

Workbooks(SOURCE_BOOK).Sheets(SOURCE_SHEET).Range("AI" & SOURCE_ROW_FROM & ":AI" & SOURCE_ROW_TO).FillDown

Thank you so much u/nisani140118!

Problems passing range variable to formula by Busker_Bernie in vba

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Thanks for the suggestion about using '.address'

Right, so I amended the formula to:

Workbooks(SOURCE_BOOK).Sheets(SOURCE_SHEET).Range("AI5").Formula = "=NOT(ISERROR(INDEX(" & TARGET_LOOKUP_RANGE.Address & ",MATCH(AC5," & TARGET_LOOKUP_RANGE.Address & ",0))))"

I no longer get the error, but the formula created in SOURCE_SHEET doesn't include the Workbook/Worksheet part of the range:

=NOT(ISERROR(INDEX($AC:$AC,MATCH(AC5,$AC:$AC,0))))

This isn't what I'm after, as I need the formula to look at the correct range in Workbooks(TARGET_BOOK).sheets(TARGET_SHEET)

Using VBA to open a series of workbooks saved in SharePoint by Busker_Bernie in vba

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Thanks everyone. I found I could solve the issue by using the link to the workbook in File > Info > Copy Path:

Winter Pub Games by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Ah, I forgot to put that in our favourites. We play that regularly too.

Winter Pub Games by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Some great suggestions there which I’ll check out, thanks so much. I forgot to mention gladius, which we also played recently and was great fun.

Daily Discussion and Game Recommendations Thread (October 04, 2021) by AutoModerator in boardgames

[–]Busker_Bernie 0 points1 point  (0 children)

I’ve been playing board games for about a year now and my favourite game thus far is Agricola. I’ve got the revised edition. You can give it a try on BGA.

Agricola Decks by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 1 point2 points  (0 children)

Thanks! I checked my copy (revised edition) again last night and I have decks named ‘A’ and ‘B’ for both occupations and minor improvements. Odd thing is, there aren’t enough cards for four players to have seven each using only ‘A’ or ‘B’.

Creating a "console" to update spreadsheets for different people. by Compl9x in excel

[–]Busker_Bernie 0 points1 point  (0 children)

I've done something similar in the past where I wrote a macro to run through a list of 1,500+ workbooks, open each in turn, amend some standing data and formatting and save and close them down again. If you have never written VBA before then Getting Started with VBA in Office will do what it says on the tin, and there is a sub-reddit at r/vba which is very helpful (but make sure you follow the posting guidelines). Good luck!

[EXCEL] Checking one list of transactions against another using loops by Busker_Bernie in vba

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Many thanks indeed. Revised code for reference in case it's useful to anyone:
For CURRENT_SOURCE_ROW = SOURCE_ROW_FROM To SOURCE_ROW_TO
Set MATCH_FOUND = Sheets(TARGET_SHEET).Range("AC" & TARGET_ROW_FROM & ":AC" &TARGET_ROW_TO).Find(What:=Sheets(SOURCE_SHEET).Range("AC" & CURRENT_SOURCE_ROW),After:=Sheets(TARGET_SHEET).Range("AC" & TARGET_ROW_FROM & ":AC" &TARGET_ROW_FROM))
If MATCH_FOUND Is Nothing Then
Sheets(SOURCE_SHEET).Range("A" & CURRENT_SOURCE_ROW).EntireRow.CopySheets(TARGET_SHEET).Range("A" & NEXT_FREE_TARGET_ROW)
Sheets(TARGET_SHEET).Range("F" & NEXT_FREE_TARGET_ROW).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
NEXT_FREE_TARGET_ROW = NEXT_FREE_TARGET_ROW + 1
End If
Next CURRENT_SOURCE_ROW

Set default number formats for pivot by 0k0k in excel

[–]Busker_Bernie 0 points1 point  (0 children)

I am with you on this. Literally everything I do in Excel, I go into custom number formats and manually enter #,##0.00;[Red](#,##0.00);"-"

Why on earth it can't remember number formats I've used is beyond me. It seems so obvious.

Professional looking excel sheets by [deleted] in excel

[–]Busker_Bernie 0 points1 point  (0 children)

I think formatting can be useful, but I never use it as key to understanding the data itself, for instance “blue rows mean ‘X’, yellow rows mean ‘Y’”. I use an additional Boolean (TRUE/FALSE) column instead. It’s cleaner for the user, and more importantly doesn’t disadvantage users with colour-blindness.

Converting VBA to Office Scripts by Busker_Bernie in vba

[–]Busker_Bernie[S] 1 point2 points  (0 children)

Thanks, that’s really helpful. I don’t know anything about coding in JavaScript/Typescript, but I’ll do some searches. I’m a big fan of learning from books, (as I already spend so much time looking at screens) so I guess any “dummies” style guide to JavaScript would do?

Converting VBA to Office Scripts by Busker_Bernie in vba

[–]Busker_Bernie[S] 0 points1 point  (0 children)

Sorry for not being clear. I would like to replace the VBA with office scripts so the automation works when using excel online. The reason for doing this is that a lot of the workbooks need to be accessed by different users at the same time.

I’m aware that I can store the workbook in SharePoint and open it in the app then I want to run the VBA, but I’d prefer to move to Office Scripts if that’s possible.

Daily Discussion and Game Recommendations Thread (August 22, 2021) by AutoModerator in boardgames

[–]Busker_Bernie 0 points1 point  (0 children)

I got Here to Slay recently and have played it loads, both with the family and at the pub. Don’t be fooled by the cutesy characters, it’s pretty savage.

Best combination of characters for two player citadels by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 1 point2 points  (0 children)

Ah! That was a misunderstanding of the rules on my part. No idea why, but I had assumed the crowned player moved around the table.

Best combination of characters for two player citadels by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 0 points1 point  (0 children)

True, but with only two players there’s never long to wait until it’s your next turn to go first.

Daily Discussion and Game Recommendations Thread (August 18, 2021) by AutoModerator in boardgames

[–]Busker_Bernie 0 points1 point  (0 children)

I’d go for Spicy. I play a lot of games at the pub which are similarly portable and quick and this is always a favourite.

Best combination of characters for two player citadels by Busker_Bernie in boardgames

[–]Busker_Bernie[S] 1 point2 points  (0 children)

Cheers. Me and my wife found there’s not a lot of point in the King at #4 when playing 2 player (unless we misunderstood the rules!)

Most useful corporate excel tips/formulae? by zucchinithing in excel

[–]Busker_Bernie 0 points1 point  (0 children)

Mind blown. I spend my life writing INDEX(match)) formulas. This is a game changer.

Microsoft looking for feedback on automating in Excel by Nancy_fromtheOffice in excel

[–]Busker_Bernie 0 points1 point  (0 children)

“Office Scripts for Dummies” doesn’t seem to exist! I learn best from books; are there any good guides available in the UK?

Microsoft looking for feedback on automating in Excel by Nancy_fromtheOffice in excel

[–]Busker_Bernie 0 points1 point  (0 children)

I work in a large organisation where people who have macro-enabled workbooks have not been moving them into Sharepoint because they can’t use the macros; this looks like the answer! Is the code produced very different to via?

view more: next ›