VBA Find Row in another sheet and add lines

[pasjauo]

Hey all,

I have a userform where my co-workers input some data from events. The button for the userform is on sheet1 and the sheet the data is put in is sheet2.

They input data first before the event and then they need to input data after the event (data before event is rows 1:10, and after event is 11:15). Two different userform but I ask for the same identification (so we can use that number to find the row)

I think i have everything else in the vba correct, i just need that row number.

My test looks like this:

Private Sub CommandButton1_Click()

Dim Regnskab As Long


Worksheets("sheet2").Activate


Regnskab = WorksheetFunction.Match(CVR.Value, "C:C", 0)


Cells(Regnskab, 11).Value = salgssum.Value


Unload Me


Worksheets("sheet1").Activate


End Sub

CC

I know the Match function is wrong. I have tried variations of both match and find, and nothing seemed to work for me.

The error usually tells me an object is required.

 

[Yongle]

If you are trying to match CVR.Value to a value in column C on Sheet2 ..

Regnskab = WorksheetFunction.Match(CVR.Value, Sheets("Sheet2").Range("C:C"), 0)

 

[pasjauo]

If you are trying to match CVR.Value to a value in column C on Sheet2 ..

Regnskab = WorksheetFunction.Match(CVR.Value, Sheets("Sheet2").Range("C:C"), 0)

Thanks Yongle.

It makes an run-time error 1004, it cannot indicate Match for the class WorksheetFunction.

 

[Yongle]

This line works fine for me

Regnskab = WorksheetFunction.Match(CVR.Value, Sheets("Sheet2").Range("C:C"), 0)

But the match is not being found, so we need to understand why. It can be for several reasons.
- What is CVR?
- Where is CVR?
- What type of value does it contain? (String? Alpha? Numeric? Date? etc)
- Where did you put the code? (in a standard module? sheet module? userform module?)

thanks

 

[Yongle]

and I should also mention that if the value being searched for does not exist in column C, then the code always fails

Handle like this

On Error Resume Next
      Regnskab = WorksheetFunction.Match(CVR.Value, Sheets("Sheet2").Range("C:C"), 0)
'deal with error here etc
    On Error GoTo 0

What should happen if a match is found?
What should happen if a match is NOT found?

 

[pasjauo]

This line works fine for me

Regnskab = WorksheetFunction.Match(CVR.Value, Sheets("Sheet2").Range("C:C"), 0)

But the match is not being found, so we need to understand why. It can be for several reasons.
- What is CVR?
- Where is CVR?
- What type of value does it contain? (String? Alpha? Numeric? Date? etc)
- Where did you put the code? (in a standard module? sheet module? userform module?)

thanks

CVR is equivalent to a VAT number. It is an input from a userform. It contains only numbers.

I have put the code in the userform on the button click.

The users input some data into a userform (the CVR being one of the inputs). The code should then search through the existing data on another sheet (sheet2) and identify the row number so we can input more data into columns in the end. Hope that makes sense.

 

[Yongle]

Thanks

Is CVR the name that you gave the textbox where the user inputs the number?

 

[pasjauo]

Thanks

Is CVR the name that you gave the textbox where the user inputs the number?

They input a CVR into a textbox which is called CVR yes. I have tried with a real "example" so I am aware of the error a wrong input will do.

 

[Yongle]

Another method for you to try

Delete the original line and replace with this

Regnskab = Sheets("Sheet2").Range("C:C").Find(CVR.Value, LookAt:=xlWhole).Row

 

[pasjauo]

Another method for you to try

Delete the original line and replace with this

Regnskab = Sheets("Sheet2").Range("C:C").Find(CVR.Value, LookAt:=xlWhole).Row

That works flawlessly. Instead of using a number i would like to use our journal number instead. This consists of following format: "xx-xxxxxxx". x is numbers only. The - in the middle makes it error - I reckon because it is only looking for numbers right?