sdadsads

[kragster]

Hi,
I have stumbled over a problem during the development of an order system and would like some advice. It's a simple system where the rows are orders and columns are different data for the orders. Some of the cells contain drop down menus and some of the cells contain formulas (VLOOKUP primarily), that are dependant of the value in other cells. I have created a simple macro that inserts a new order into the table:

Sub butInsertNewOrder()

Range("A4").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Rows("100:100").Copy
ActiveSheet.Paste
End Sub

As you can see it inserts the value of all cells in row 100 into the first empty row. The idea was to insert as much data as possible into row 100 (including drop down menus and formulas), so that the user of the system wouldnt have to fill out all the cells manually. The problem is VLOOKUP. It is dependant on the cell to the left of it. Is there any way to make this a relative reference or is this a stupid way to make this system?

 

[kragster]

Im sorry about the header. It was a mistake and can't seem to find an edit button

 

[SkipVought]

Hi,

Sub butInsertNewOrder()

  Rows("100:100").Copy Range("A4").End(xlDown).Offset(1)

End Sub

"...The problem is VLOOKUP. It is dependant on the cell to the left of it..."

After you paste, is the cell not still to the left of the VLOOKUP?

What happens when your data table reaches row 100? Might it be better to put this row at the TOP?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[kragster]

Hi,
Your absolutely right about the position of the row, but thats not the problem. My VLOOKUP looks like this:

=+LOPSLAG(B6;'Contact info'!$A$3:$Y$1000;2;FALSK)

Its the danish version of excell. Hope you understand it anyway This should be the formula of cell C6. As I understand VLOOKUP it looks for the value B6 in the sheet "contactinfo". Now when the user inserts a new order into row 8 for instance, the VLOOKUP should be as follows:

=+LOPSLAG(B8;'Contact info'!$A$3:$Y$1000;2;FALSK)

 

[SkipVought]

"...the VLOOKUP should be as follows:"

I got the SHOULD be, but what is actually happening?

You described an INSERT, "...when the user inserts ..."

How is this occuring?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[xlbo]

That is the default behaviour if you enter formulae by code - any relative range references will move accordingly

As per Skip - what is happening that is not what you think should happen ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kragster]

Youll have to excuse me, I don't have much experience using VBA and excell and less decribing it.

I have a VLOOKUP in several of the cells. These VLOOKUPS takes the value of the cell to the left of them, then looks in another sheet for the same value and then returns a value acording to this. It would be nice if these formulas could be in that line 100, so when the users inserts a new order, the formulas are inserted into the respective cells. For instance, this is the formula that should be in cell C6:

=+LOPSLAG(B6;'Contact info'!$A$3:$Y$1000;2;FALSK)

But when inserted into row 15 of the ordersystem it shouldn't look for the value of B6, but instead for the value of B15. Does this make any sense?

 

[kragster]

You described an INSERT, "...when the user inserts ..."
How is this occuring?
Skip,"

There is a button on the sheet that executes the makro from the first post. The macro then copies row 100 and inserts it in the first empty row. In this row 100 (a hidden row), the grand scheme was to put all formulas and preexisting data, so that the users wouldn't have to copy formulas and to ease his job. After he inserts the row, he then fills data for the order. It's not possible to upload pictures here?

 

[xlbo]

Not possible to upload pictures but what you describe is the default behaviour of excel. If you copy the formula:

=+LOPSLAG(B6;'Contact info'!$A$3:$Y$1000;2;FALSK)

From C6 to C15, it should change automatically to:

=+LOPSLAG(B15;'Contact info'!$A$3:$Y$1000;2;FALSK)

which I assume is what you want. Can you descibe what is happening now if that is not what occurs....


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

To restate what Geoff has already stated so well, if this were in C100...
[tt]
=+LOPSLAG(B100;'Contact info'!$A$3:$Y$1000;2;FALSK)
[/tt]
when you copied row 100 to row 6, you would get in C6...
[tt]
=+LOPSLAG(B6;'Contact info'!$A$3:$Y$1000;2;FALSK)
[/tt]

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[kragster]

Hi,
I'm so embarassed. Your absolutely right! The problem was the references in the VLOOKUP, not what I described in here. Thanks a lot for your help! It works now.

 

[WalkerEvans]

In a similar situation I avoided the "what happens when your data table reaches 100?" problem by placing my copy-from row just off the visible screen and using insert and offset to put it where it needs to be. A snippet of this code follows:

Selection.EntireRow.Insert
  'Move down to the Master line
  'and copy line (with formulas)
  ActiveCell.Offset(41, 0).Activate
  Selection.EntireRow.Copy
  'Move back up to the inserted line,
  'paste copied line, and continue
   ActiveCell.Offset(-41, -0).Activate
   ActiveSheet.Paste

This completely eliminates the chance of a user overwriting your Master line. Since users, by definition, don't know what you've done with the code, and since the line being copied is hidden, they aren't likely to notify you until after they've already broken it!

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[SkipVought]

I'd suggest putting your row template on another sheet that is hidden.

Sub butInsertNewOrder()
[b]
  Sheets("OtherHidden").Rows("1:1")[/b].Copy Range("A4").End(xlDown).Offset(1)

End Sub

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}