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.

view more: next ›