Search results

Try replacing ""MC"" with: CHR(34) & "MC" & CHR(34)

Ok. Got it. Add this to the code that originally shows your userform: ... load userform1 ... rowcount=Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count Me.labelcontrol.caption = format(rowcount,"00000") ' this would display the control number as 5 digits, with leading zeros ...

You can use VBA to automatically refresh the data on calculate (you might change this to workbook_open() if it gets laggy: Private sub workbook_open() Sheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh End Sub To the data, add a 'month' column (eg D2 formula =Month(A2) - you'll...

I changed the "A"s to 1 and the "B" to 2 and moved the code to the "This Workbook" code area, (not in a module), and it worked just fine.

Seems like it would work as is. From what you've written, you want to add some sort of control number to the form? Can it just be a sequential number? If so, just use the rowcount as your control number, and use the format command in VBA to pad it to 5 characters: Private Sub cmdOK_Click()...

Did you mean E2? You just have a 0 in it. It will autofill with the same number (0,0,0, etc.). If you meant to calculate, then you should have =C2-D2 in cell E2. With that in place, the following code will do the autofill: Sub autofillblocks() Dim lastrow As Integer Dim range1 As String...

I would think that if it's something you've purchased, you should go to the company that made it. I thought you had your own spreadsheet.

You could also use =TEXT(A1,"00000") or =TEXT(A1,"000") This will handle either 3 or 5 digit codes: =TEXT(A1,IF(LEN(A1)<4,"","00")&"000")

Can you upload your workbook?

activesheet.range("BA2").select if selection.value = "" then 'just in case BA2 is empty - I assume you want it filled as well, if it is selection.value = "Blah" 'or whatever you want here End if loop1: selection.offset(1,0).select if selection.value = "" then...

ActiveSheet.Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Select That will find the next visible cell below A1

if you can get the placement of the spaces done, then replace the 4th line of your code with this: mystring = application.proper(mystring)

The left() command gets the text starting at the left-most character, and going to the right for the number of characters specified - you have 1 specified, so it only grabs the left-most character. Unless everyone has the same number of letters in their first and last name, your code won't work...

So you want it without the 'SCH5'! prior to each cell reference in your if equations? I tried and cannot duplicate. Upload a sample so we can see what you're dealing with. If you need an easy way to remove part of the equation, use a replace all - press ctrl-F, choose replace, type 'SCH5'! in...

I used a custom function: Function grabcodes(RepName As String, data As Range) Dim rowcount As Integer Dim toprow As Integer Dim leftcol As Integer Dim topleftcell Dim repcodestg As String rowcount = ActiveSheet.Range(data.Address).Rows.Count toprow = Range(data.Address).Row leftcol =...

Can you upload the script?

Please upload an example file... I don't understand your question.

Looks like you've formatted the month numbers as a date (when you see a date, it is stored in excel as the number of days since 12/31/1899, so 1 = 1/1/1900, 2 = 1/2/1900, etc) - you can either set it back to general, or change the values to the first of the respective month (which would probably...

VBA to the rescue - hopefully you haven't been working on it too long: Sub copydatabars() Dim target As String For i = 2 To 9 'first row w/o data bars to last row with data target = "=Sheet1!B" & i ActiveSheet.Range(Cells(i, 3), Cells(i, 26)).Select 'change the 26 if you have more than 24...

Have a column that holds the pledge amount. Edit the conditional formatting for your Data bars; change the max type to formula, and set the equation to the cell that contains the donation amount. You'll have to do it for each row, since the data bars don't like relative cell addresses.

=LINEST() will generate a linear best fit =LOGEST() will do a log fit That's about as fancy as Excel's built-ins go. There are several add-ins that would probably do a better job.

I'd just use a bias (whatever the date is for your first data point), and add that to your scroll bar output.

One thing to check - Since you had states in your example, I had the code only look 51 times (50 states + DC). You'd need to change the maximum number in the second for loop. The way that I would do that is to have =COUNT(B2:B1000) (or whatever the first code is through some future maximum...

Yes; you would have two options there; you could insert a blank column in the correct place, or write a second macro that would do that, which should be pretty easy. Thinking about it, the columns wouldn't actually have to be in alphabetical order; they could be in whatever order you would like...

I think you're handling this wrong - if you start moving around columns in the Delivery Planning sheet, you're going to risk losing data. There will bee a lot of other complexities (one big one is that state postal abbreviations are not in the same alphabetical order as the states themselves)...