Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I have a workbook that has 22ish worksheets on it. I want to use an input box that allows the user to select a range, and then get the sum of that range. After it does that I want to use that range for each worksheet. I'm using this(cutting out a lot):

Dim prevRow as Range

Set prevRow = Application.InputBox(Prompt:="Please select the range to reference", Title:="Range selection", Type:=8)

moveIN = WorksheetFunction.Sum(prevRow)

This works fine for the first worksheet, but I can't use the inputted range for any of the other worksheets. It just gives me the same values from the first worksheet. Is there something I'm missing.

# ¿ Dec 4, 2009 05:05

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 12:29

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

TheAngryDrunk posted:

What do you mean by saying you "want to use that range for each worksheet." Are you trying to total up all of the cells across each worksheet?

If so, you could loop through each worksheet:
code:
Dim prevRow As Range
Dim wksht As Worksheet

Set prevRow = Application.InputBox(Prompt:="Please select the range to reference", _
Title:="Range selection", Type:=8)

For Each wksht In Worksheets
    moveIN = moveIN + WorksheetFunction.Sum(prevRow)
Next wksht

Sorry, I'll give a better idea for my project. I have one excel file (excelfile1) that has 21 worksheets. There's another excel file (excelfile2) that also has 21 pages. I've set it up so that when I run the macro on excelfile1, it opens up excelfile2 and asks for a range to be selected and input on a worksheet. It then takes the sum of that data and puts it in excelfile1. What I want to do is then use that inputted range, and do the same thing but applied to the second worksheet on excelfile2.

The problem is, it seems to have set the range as let's say $U$51:$U$65 on the first worksheet it's inputted. Is that a little clearer?

# ¿ Dec 4, 2009 14:56

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

TheAngryDrunk posted:

What do you mean by saying you "want to use that range for each worksheet." Are you trying to total up all of the cells across each worksheet?

If so, you could loop through each worksheet:
code:
Dim prevRow As Range
Dim wksht As Worksheet

Set prevRow = Application.InputBox(Prompt:="Please select the range to reference", _
Title:="Range selection", Type:=8)

For Each wksht In Worksheets
    moveIN = moveIN + WorksheetFunction.Sum(prevRow)
Next wksht

I worked on this more today, and this macro doesn't work for me. It just gives me the sum from the first worksheet.

# ¿ Dec 7, 2009 23:24

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Right after the if statement but before the sh.cells(x,3) part, you can put:

code:

if Sh.Cells(x, 3) <> c.Offset(, 2) then Sh.cells(x,3).Interior.ColorIndex = 5

I think this should do what you want.

# ¿ Mar 22, 2013 03:42

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I have no idea why this isn't working so hopefully someone can point out what's wrong. What this does is checks to see if a main folder exists by testing the length of a directory, and if it doesn't then it creates it. This part works fine.

After that, a loop starts creating folders for items in an array if they don't exist (supposedly). Here's the code (I split up the 2nd to last line to stop table breaking):

code:

    If Len(Dir(destinationfolder & newfolder)) = 0 Then fs.createfolder destinationfolder & newfolder
    destinationfolder = destinationfolder & newfolder
    For i = 1 To UBound(arrdata)
    If Dir(destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)) = vbNullString Then fs.createfolder 
destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)
    Next i

The first two lines work perfectly, the folder exists and it's not creating anything. I've tried both len() and have it checking for vbnullstring as well, both say the folder doesn't exist and then tries to create said folder followed by an error saying the folder already exists. I setup a temp variable to read both len() and dir() and both return with 0 and nothing. Any ideas?

Edit:
Well that was quick. Ends up I need a vbdirectory in my dir statement. No idea why the first part was working but oh well.

Harry fucked around with this message at 23:02 on Apr 26, 2013

# ¿ Apr 26, 2013 22:45

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Old James posted:

I'm surprised you got it working. It appears to be missing some ELSEs and END IFs that should stop the script from running.

If it's on the same line you don't need an else or end if.

ShimaTetsuo posted:

I'm also surprised at the crazy use of Dir when you seem to have a FileSystemObject right there from which to call FolderExists.

I don't deal with FileSystemObject very much obviously.

# ¿ Apr 27, 2013 20:22

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

DirtyTalk posted:

Okay, there is a way to setup the program to sift through every workbook in a certain directory? It's not a specific number or set of workbooks as the workbooks are constantly being added to.

I guess you're right, the opening and closing of the workbooks sound like this will be the biggest issue. Especially because Im talking millions of records here. Any ideas on how this could be streamlined?

http://www.ozgrid.com/VBA/loop-through.htm
This will probably help you. It sounds possible with find, assuming the data you're looking for is somewhat unique.

# ¿ Apr 30, 2013 18:53

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I'm probably being really stupid here, but sort each group by country name and then put the columns next to each other.

# ¿ May 10, 2013 19:20

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Xenoborg posted:

I've got a large excel sheet with about 50 tabs, each with a similar filtered list. Is there any way to globally set these filters?

Playing around with record I've come up with the below to do the active sheet. Is there an easy way to make it do all sheets, iterate through sheets without having to name each, or all filtered lists in the document?

ActiveSheet.Range("$A$1:$M$500").AutoFilter Field:=4, Criteria1:="Type A"

Probably something like this (I don't use auto filter so I'm just basing this off your code)

code:

dim x as integer
for x=1 to 50
Activeworkbook.sheets(x).Range("$A$1:$M$500").AutoFilter Field:=4, Criteria1:="Type A"

next x

# ¿ May 21, 2013 16:25

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Anyway you can post the files?

# ¿ May 22, 2013 00:45

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

It sounds doable with a couple of set controls. Is there any reason the workbooks have to be selected, and not just something that you run daily?

# ¿ Jun 7, 2013 17:08

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Old James posted:

Read the next post after that....

You're like some kind of wizard that could see into the future.

# ¿ Jun 13, 2013 18:21

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I've got an interview tomorrow that wants Advanced Excel skills, including "including use of nested formulas and data mining." I no problem using nested forumals, and I'm assuming data mining has to do with pulling from an sql database. I use vlookups all the time, and have a pretty good grip on pivot tables. Any other suggestions to brush up on? I don't think I should have a problem, but I use macros more often than regular functions would rather not get caught off guard by something that people would think I should know.

# ¿ Jun 17, 2013 00:34

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

1. Use '. So it should be '(Q1) Performance'!A1.
2. Just use text(E6/E7,"0.0%")

# ¿ Jun 17, 2013 13:00

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I would check the inputs/outputs if the first and second iteration works. Just from the code you posted, I don't see why it would just stop working correcting for the third. Also, if you didn't know you can use F8 to go through this step by step and see what the variables are throughout.

# ¿ Jul 8, 2013 17:13

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Total Meatlove posted:

My colleague has somehow managed to get his copy of Excel 03 to insert a ^ at the start of every cell in any spreadsheet he opens. What has he done and can it be switched off?

He's not saving them as Lotus 1-2-3 files or something like that is he?

# ¿ Oct 4, 2013 20:28

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

TheEffect posted:

I have a protected Excel document out on SharePoint and I want employees to be able to edit any cell in just one column, and once they've edited the cell I'd like the document to lock the cell and then protect the sheet again on save. So basically-

-Sheet is protected.
-One column with highlighted, but otherwise empty, unprotected cells that users can edit.
-Once the document gets saved (or a cell is edited?) a script will check to see if any cells, in that column, with data in them are unlocked and/or formatted with highlighting. If so, the script will un-highlight the cell and lock it so that it cannot be edited without protecting the sheet.

Not sure that's a "small Excel question" but if someone could point me in the right direction, or tell me if this is even possible, that would be great.

So do you want it to save and lock on editing a cell, or save and lock just when they're done with the file?

# ¿ Oct 14, 2013 21:32

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

You can use this if you want to ensure it always goes in column B, no matter where in the row you select.

code:

Cells(ActiveCell.Row, 2) = "rep"

# ¿ Oct 24, 2013 16:58

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I'd say vlookup, sumifs, and maybe a pivot table of some sort.

# ¿ Nov 21, 2013 17:10

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 12:29

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Has anyone ran into auto calculate not working properly after setting it to manual in a macro? It says it's automatic under options, but won't actually calculate anything until you type something into a cell or press the backspace. Pressing delete does nothing.

# ¿ Feb 13, 2014 00:19

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread