VBA code problem

[KlausW]

Hi Excel helpers.

I use this VBA code to register who eats when, I have tried to change it so I can use it to register what I would like to buy I my sheets whit some item, but I run into problems and cannot solve them myself. Someone who can help.

All help will be appreciated.

Regards Klaus W

Sub Prisliste_Rektangelafrundedehjørner4_Klik()

'If Target.Cells.Count = 1 Then Exit Sub

'If Intersect(Target, Range("A8")) Is Nothing Then Exit Sub

Dim WkRng, DestRng, SrcRng As Range

Dim TidCol, TidRow, c As Integer

With Sheets("Prisliste")

Set WkRng = .Range("A9:A4000") 'Item No.

Set DestRng = .Range("c9:c4000") 'Quantity

On Error GoTo Ooops 'Error handler

TidCol = Application.Match(.Range("A9:A4000"), Sheets("Prisliste").Range("P9:P4000"), 0)

TidRow = Application.Match(.Range("C9:C4000"), Sheets("Prisliste").Range("R9:R4000"), 0)

End With

Application.EnableEvents = False 'Stop this change event code triggereing itself and looping forever

WkRng.Value = Sheets("Prisliste").Cells(TidRow, 3).Resize(7, 1).Value

For c = 0 To 2

Set SrcRng = Sheets("Prisliste").Cells(TidRow, TidCol).Offset(0, c).Resize(7, 1)

DestRng.Offset(0, 2 * c).Value = SrcRng.Value

Next c

Ooops: 'Error message if there is error.

On Error GoTo 0 'set error handling back to default

Application.EnableEvents = True 're-enable events handling

End Sub

 

[BSALV]

your application.match has 3 parameters,
the first is a single value or cell, the 2nd an array or range, and the 3rd if you want an exact match or not.
So your 1st parameter isn't a single value.
Further what do you want to compare ?
Normally you look horizontal and vertical to find a cell, Here ?

Can you show a little bit of your layout ?

 

[KlausW]

your application.match has 3 parameters,
the first is a single value or cell, the 2nd an array or range, and the 3rd if you want an exact match or not.
So your 1st parameter isn't a single value.
Further what do you want to compare ?
Normally you look horizontal and vertical to find a cell, Here ?

Can you show a little bit of your layout ?

In a Combo Box I write the start letters, or whole word for an item. In column B (starting in B9 and to B3669) the items that starts with the letter or word I write in the Combo box will be show her. If I want to order an item, I type the quantity in column C.
In column A is the items no. and in column B the items name.
Now if I right another letter or word in the Combo box, the new item will be show, but the quantity will not follow the item. But The quantity will still be in column C form the last search.
I make an example.
In the Combo box I type milk, an in column B all the items with milk will be showing. I then order 10 quantities (in column C) of skimmed milk. Next, I type yeast in the Combo box, an in column B all the items with yeast will be showing. I then order 1 quantity (in column C) of yeast. And so on.
When I am finish whit my order, I use the Data > Filter in column C to filter the numbers so I don’t get any cells that is empty or got a zero.
Is it understandable? I have a VBA code that can do a bit the same but I cannot figure out how to fix it so that dove.
KW

 

[KlausW]

Ok so thats is the problem, I try to explain in the text what I want to do. KW

 

[KlausW]

Ok so thats is the problem, I try to explain in the text what I want to do. KW

good evening, the sun has set in denmark, and i have gone to bed. I will write and explain tomorrow. thanks for the help so far. KW

 

[KlausW]

Hi Bsalv

I will try to explain it with pictures.

Image 1 Combo Box I am looking for the item.

Picture 2 I search for skimmed milk in the Combo box, the result appears in column B and order the number in column C

Picture 3 Now I search for yeast in Combo box, the result appears in column B, now the problem is in column C. The number I ordered of skimmed milk is still there.

Picture 4 Then I order yeast in column C.

Picture 5 When I use Data> filter, only the yeast appears.

Pictures 6 This is how I want it to look like there is both skimmed milk 10 liters and yeast 1 pack.

Picture 7 I was thinking if you could use column P as item number and column R number, which helps columns.

I have a VBA code which almost does it but I cannot figure out how to fix it so it does what I have explained to. I send it along in the hope that it can inspire.

I hope it makes sense.

Greetings from Denmark KW

 

[Fluff]

Duplicate to: quantity has to follow the item

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.