Desperately seeking some help! (Please)

[Peter Long]

I have had some trouble on a project I have been working on for
some time. I have tried numerous approaches, each with undesired
results. (VAB Code)

The project I am working on has become quite complex, so rather
than bother you with my spaghetti code, I have made a simple example
of what I am trying to do.

http://www.geocities.com/rockytophubby\sample.html

The sheet tracks People and how many fruits they had each day.
Day1 is where the information is Inputted, Day2 will double the DAY1
numbers, and Day three will triple the DAY2 Numbers.

The challenge I am faced with: On DAY1 every time ANY information
is inputted (even if there is no name) have a new Row AUTO inserted
for the next person on ALL three days. (Keeping in mind each day has
different formulas, so the Formatting and Formulas must be copied from
the line above it on each sheet.)

ANY help I can get here would be greatly appreciated, as I know
this is no task for a beginner...

 

[JulieD]

Hi Peter

based on your simple example here's some code (which could probably be
cleaned up a bit but i've left it rather longwinded in case you need to edit
it) which when assigned to a button on the form adds the new lines to each
sheet then prompts for the name & number of each type of fruit for the
starting day. You could name the worksheet button "ADD NEW PERSON" or
similar

Sub AddNewRecord()
Rows("3:5").Select
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToLeft).Select
Sheets(Array("Day1", "Day2", "Day3")).Select
Sheets("Day1").Activate
ActiveSheet.Paste
Selection.End(xlToLeft).Select
ActiveCell.Offset(2, 1).Select
ActiveCell.Value = InputBox("Enter name", "Enter name")
Sheets("Day2").Select
Sheets("Day1").Select
ActiveCell.Offset(0, 1) = InputBox("Enter number of apples.", "Apples")
ActiveCell.Offset(0, 2) = InputBox("Enter number of oranges.",
"Oranges")
ActiveCell.Offset(0, 3) = InputBox("Enter number of grapes.", "Grapes")
Application.CutCopyMode = False
End Sub

Please post back if you need assistance getting the code in the right place
or linked to the button or talking about something other than fruit!

Hope this helps
Cheers
JulieD

 

[Peter Long]

Thanks for the effort Julie, I would perfer to not use a button to
more rows, but rather use some type of value check on input row to
auto add rows.

You efforts have not been wasted though, I will hold on to your
code and review it step by step for future refrence. ( I am pretty new
to Excel proggraming, but have done quite a bit of C++ and pascal
programing) I am eager to learn more....where would the code you send
normaly be put? In the code panel (VBA)

 

[JulieD]

Hi Peter

to use the code, right mouse click on a sheet tab and choose view code
on the left of the VBE window you should see the project explorer with your
workbook name in bold (if you can't see this choose view / project explorer)
then under this you should see Sheet1, Sheet2, Sheet3, ThisWorkbook ... as
this code is run via a button it needs to go in a module sheet - ensure that
you're clicked on your workbook name on the left and choose insert / module
... this will appear under ThisWorkbook as module1 ... double click on it and
a white sheet of paper should be displayed on the right, copy & paste the
code into there.

as for having the code "autorun" based on some field's contents - this can
be done ... what field & what value do you want to "initiate" the code?

Cheers
JulieD

 

[Peter Long]

I wanted a new line inserted if ANY of the values are entered
under the last blank rows under Family or friends. (Added to EACH day)

Because of the formulas on each day are different, and could also
be different for Friends and family, I would want to copy the Cells
above it on each sheet independently.

On DAY1 - Entering 2 on C5

DAY1 = Insert Blank Row at ROW6 (copied from DAY1 Row5)
DAY2 = Insert Blank Row at ROW6 (copied from DAY2 Row5)
DAY3 = Insert Blank Row at ROW6 (copied from DAY3 Row5)

On DAY1 - Entering 6 on D8

DAY1 = Insert Blank Row at ROW9 (copied from DAY1 Row8)
DAY2 = Insert Blank Row at ROW9 (copied from DAY2 Row8)
DAY3 = Insert Blank Row at ROW9 (copied from DAY3 Row8)

This way I could have different Formulas and Cell formats on each
DAY and for each section of Friends and family copied. I would want
this to continue endlessly. (I hope this explains well enough what I
am trying to do….)

I am actually working on 3 projects, each requiring this type of
line coping and inserting. This has me stumped on all three :-< If I
can get it working on this sample spread sheet, I can adapt it work on
all three.

 

[JulieD]

Hi Peter

the problem i'm struggling with is that i can't figure out how to tell excel
that you've entered a value in the "last" blank cell of a section. I'm
thinking along the lines of a worksheet_change event and dynamic named
ranges where when the sheet is opened the number of cells in each range is
calculated and then if the range size changes (ie you enter another value
in) then the copy & paste event happens. Sounds great, but then we have the
problem of storing the range sizes somewhere as once code terminates the
variables loose their "memory" - are you happy to have a hidden 'junk' sheet
to store this information on?- that's if someone else reading this post
doesn't have a better idea.

Cheers
JulieD

 

[Peter Long]

I had thought about the Hidden worksheet to store variable values in,
but just seems like there should be an easier way. Although I am
fairly new to excel, I have some background in Programming. I can
follow the logic easy enough, but have some trouble with the commands
and syntax. Here is an idea I though of this morning…

If I Put a hidden Word "END" in the Cell A under the Last empty cell
(Font same as Background)

---After ANY cell value is changed on DAY1 Perform the following
checks:
===================================
----VAR---

CURRENTROW = Current row of the Cell value that was just changed
CURRENT = Status of Current row Values { Full, Empty, None }
BELOW = Status of Next row Values { Full, Empty, None }

----FORMULAS----

CHECKSTATUS (CHECK = ROW TO CHECKED)
IF COUNTBLANK(CHECKB:CHECKE) < 4 Then return FULL
** IF CELL ( ROW = CHECK , Column =A ) = "END"
Then Return value NONE
Else return value EMPTY
----Code----

ASSIGN
CURRENT = CHECKSTATUS (CURRENT)
BLEOW = CHECKSTATUS (CURRENT+1)

If (CURRENT = EMPTY) AND (BELOW = FULL)
THEN delete Row (CURRENTROW on EACH SHEET)

If (CURRENT = FULL) AND (BELOW = NONE)
Then Copy current Row DAY1 (Formulas and Formatting) and
Insert at CURRENT +1
Copy current Row DAY2 (Formulas and Formatting)
and Insert at CURRENT +1
Copy current Row DAY3 (Formulas and Formatting)
and Insert at CURRENT +1
===================================
** Another way, instead of using the word END: (This might be a
"Cleaner" method) Compare the formatting to the row above it, if it is
not the same, then we know to return the Value NONE else return the
value EMPTY.

The logic seems to be tight here, but now actually converting it
to code…..I am lost on LOL. If someone could help out on this part
and Email it to me (My email is on the original web set of the
Sample.xls ) I can post it on that web site in case others would like
to see it as well.

 

[Peter Long]

Something I had not considered……What if I have other information on
Day1, Such as Name, Date, town etc above or below the Data Areas
rows. We would need some way to restrict what areas on DAY1 it would
apply these checks. I know this is getting pretty complicated :-(