Posts by Bubbis Thedog

I figured out something that worked. FF1 is way out of the way of the data in the sheet. If this is a bonehead way to do it, feel free to tell me! hahaha Thanks, you all, for any suggestions.

Dim InCell As Integer


Range("FF1").Select
ActiveCell.Value = InputBox("Enter the new state abbreviation", "New State Entry")
    For InCell = 132 To 414 Step 141
    Range("FF1").Select
    Selection.Copy
    Range("A" & InCell).Select
    ActiveSheet.Paste
Next InCell


Application.ScreenUpdating = True

Bubbis Thedog

Hello, folks.

All I'm trying to do is enter an InputBox value, starting in cell A132 and then proceeding to A273 and AA414 (141-row increments). I've written the following code but it is not working. Any suggestions would be greatly appreciated.

Sub Insert_State()
Application.ScreenUpdating = False


Dim InCell As Integer


    For InCell = 132 To 414 Step 141
    Range(Cells(InCell, 1)).Select
    ActiveCell.Value = InputBox("Enter the new state abbreviation", "New State Entry")
Next InCell


Application.ScreenUpdating = True
End Sub

Bubbis Thedog

Tom, you da man . I almost had it right --that is, how you modified your previous code. I know that this was easy for you, but I learned a lot from you today, mainly 1) working from the bottom up (much easier than thought it could be done) and 2) about "ScreenUpdating." I greatly appreciate your time and I thank you very much.

Bubbis Thedog

Thanks for the help, Thomach. I understand you when you suggest to go about this by starting from the bottom up and it makes perfect sense. If I was just copying and pasting, I could start my iterations at the top because rows are not being added; I get what you're saying.

Initially, I thought that the code would reflect a sequence. The following begins at row A132 for inserting the rows:

Let Scale = 136
Let Inc = 5

S = A132, A(132 + (1)Scale + (1)Inc), A(132 + (2)Scale + (2)Inc), A(132 + (3)Scale + (3)Inc),..., A(132 + (n)Scale + (n)Inc)

So the starting point to insert five rows is row A132; the next five rows' starting point to be inserted is A273; the next five rows' starting point to be inserted is A414 and so on...

For copying, the sequence begins at A127:

S = A127, A(127 + (1)Scale + (1)Inc), A(127 + (2)Scale + (2)Inc), ...,A(127 + (n)Scale + (n)Inc)

So, after the rows have been inserted throughout the worksheet, the starting point for the first selection to be copied is row A127; the second starting point is row A268; the third is A404 and so on...

Then comes the pasting of the five-row selection: S = A132,A273,A414

I tried out your code but could not get it to work, Thomach. I changed the MyRow = 1220 to 408, which is the last row position in the array. Is there something else that I need to change in your code? I keep getting a "400" error. Hope I explained a little better what my intentions were. Thanks so much for taking the time to help.

Bubbis Thedog

Hello, folks!

Below is what I am trying to accomplish:

1) Insert rows from A132 to A136, then from A(268+5) to A(272+5), then from A(404+10) to A(408+10), then from A(540+15) to A(544+15)...

2) Next, copy rows A127 to A131 and paste into row A132; copy rows A(263+5) to A(267+5) and paste into row A(268+5); copy rows A(399+10) to A(403+10) and paste into row A(404+10); copy rows A(535+15) to A(539+15) and paste into row A(540+15)...

3) Lastly, replace cell A132 with <inputbox input [user simply enters the abbreviation of the new state that is being added]>, then replace cell A(268+5) with <same inputbox input>, then replace cell A(404+10) with <same inputbox input>...

I know how to do the simple stuff for one iteration:

Sub InsertRows_CopyPasteStateInfo()


	Range("A132:A136").EntireRow.Insert
	Range("A127:A131").EntireRow.Select
	Selection.Copy
	Range("A132").Select
	ActiveSheet.Paste


End Sub

Unfortunately, I'm not quite sure how to accomplish this for the multiple iterations that I defined above. I'm also not exactly sure about how to add my InputBox. Attached is a file of a partial version of the data that I'm working with. I'm currently learning VBA, and any assistance will be greatly appreciated!

Thank You,

Bubbis Thedog

Brandtrock and shades: THANKS! I had actually created a pivot table just like that, Brandtrock, but it wasn't what I was looking for really. Thanks so much for taking the time to help. shades, I have no idea why I didn't think of concatenating those three columns; I concatenated two columns prior to that to simplify things, but didn't think of adding a new column. I was soooo close on that formula; shoot! I'm getting there with your alls' help! Thanks to you for spending the time to help as well!

Bubbis Thedog

Thanks for taking the time to help me, you two. I must not've explained what I wanted properly. Here, I'll give it another try and attach my newly formatted sheet:

1) User enters in cell B1 the name of the Field Rep who they want to retrieve data for.
2) User enters month of the year that they want to see data for for the Field Rep that they entered in cell B1.

Result:

EXAMPLE: Let's focus on Gary's personal lines contract volume in August (so user types "Gary" in 'Sheet2'!B1 and "Aug" in 'Sheet2'!B2). RESULT: Cell 'Sheet2'!B9 is equal to the "Current Month" value (under column D in Sheet1) in the row containing "Aug" in column A, "Gary" in column B, and "ContVolPers" in column C [D2= 1800].

EXAMPLE 2: Let's focus on the finance charges for commercial lines contracts attained by Laura in August of the prior year (so user types "Laura" in 'Sheet2'!B1 and "Aug" in 'Sheet2'!B2). RESULT: Cell 'Sheet2'!C22 is equal to the "Prior Year" value (under column E in Sheet1) in the row containing "Aug" in column A, "Laura" in column B, and "FinChrgCom" in column C [E15 = 1200].

What I'm looking for is the formulas to enter into Sheet2 such that their results reflect the Field Rep and month that the user enters.

Hope that clears things up, and thanks so much!

Bubbis Thedog

I was not specific enough. I meant:

For the first row in Sheet1 that contains 'Sheet2'A1 AND 'Sheet2'A2 [and also includes "ContVol" in column C and "Per" in column D], return the value from "Current Month",'Sheet1' (column E) which corresponds to that row.

Sorry for the screw-up. Thanks again to anyone who can offer some assistance!

Bubbis Thedog

Hello, folks!

I need help with a formula, please. I've attached the workbook that shows how the raw data is laid out. Below is what I've tried to accomplish to no avail:

For the first row in Sheet1 that contains 'Sheet2'A1 AND 'Sheet2'A2, return the value from "Current Month",'Sheet1' (column E) which corresponds to that row.

In the cell where I want this value, I've tried to use INDEX and MATCH, but I can't figure it out. I've also searched the site, and can't seem to find the info I need. Any help would be greatly appreciated!

Bubbis Thedog

Unfortunately I'm not well accustomed to using arrays (which I suspect that you'll need if you want to preserve the current format of your schedule sheet), so I modified the layout of your schedule sheet. This isn't the best way to do what you want, I'm sure, but it should work. All you need to do is enter the value for the location in the formula on sheet 2, such that it corresponds with the location number in column A. I didn't write a formula for the weekly totals because it was becoming cumbersome. I'd like to also note that your info in sheet one for each user appears to be dynamic, which requires code. That's beyond my scope. Hope this helps a little at least!

Bubbis Thedog

Thanks to you, too, Derk! Man, that was messing with head...

Bubbis Thedog

Good deal, Will. I knew it was something little that I was missing! :drunk:

Good advice on the Worksheet names, too.

Thanks so much for your help!

Bubbis Thedog

Is this a start?

Bubbis

Hey, everyone.

First of all, I've attached a workbook containing my data and a related PivotReport. Notice the "Blank" row and column in the PivotReport. Is something wrong with my OFFSET() formula for my named field (Excel_DB)? I'm wanting the PivotReport to reflect a dynamic array in "Worksheet." Everytime a record is entered, the data needs to reflect in the PivotReport.

Second of all, I'm not quite getting how "Refresh Data" works. Refreshing the PivotReport is what needs to be done after entering new data in the source worksheet, I assume; but wierd things begin happening when I Refresh in different cells of the PivotReport. For instance, if you Refresh in the lowest, rightmost cell, the Report condenses only to display October. When I Refresh again in that cell, All data reappears. What's up with Refresh? All I want to do is have the data in the Worksheet reflect on the PivotReport.

Thanks for any help!

Bubbis Thedog

I've had the same problem before. I think word wrap only works for a maximum of 1024 characters in a cell.

Bubbis

The font is there now, in the list! :yikes: WTH?

If you use NETWORKDAYS, here's what you'll end up with. Note that I used a formula in column B to eliminate the possibility of a mistake counting days between 1/1 and 2/1; 2/1 and 3/1; etc.

Bubbis Thedog

Thanks, Barbarr. I'm at a loss myself. That was a nice font, too! :censored:

Bubbis

Actually, That Did Work...

But before adding another 13 numbers in the right-most unpopulated column, the blue Autofilter arrow needs to be set to "All." Then the 13 numbers can be added, and then set the blue arrow back to "Top 10."

Ok, I'll stop going on about this... hahaha

Bubbis

It was a brand spankin' new document.

Bubbis Thedog