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]

The - in the middle makes it error - I reckon because it is only looking for numbers right

yes

Your journal number is in this format 12-3456789
red = prefix (2 characters)
blue = suffix (7 characters)

Based on string 12-3456789
Q1 what is in the textbox?
Q2 what is the value to be matched in sheet2?

Q3 Is there any danger of leading zeros causing a problem?
(- could either prefix or suffix begin with 0)

 

[pasjauo]

yes

Your journal number is in this format 12-3456789
red = prefix (2 characters)
blue = suffix (7 characters)

Based on string 12-3456789
Q1 what is in the textbox?
Q2 what is the value to be matched in sheet2?

Q3 Is there any danger of leading zeros causing a problem?
(- could either prefix or suffix begin with 0)

Yes, thought it would cause problems. The reason why we want to use that instead of the VAT number is that the same VAT number can appear more than one time, but the journal number only comes once.

Q1: I dont understand the Q. They input the journal number, which in theory could be exactly 12-3456789.

Q2: The value to be matched is what they input as journalnumber in the first entry. You can divide it up in to two entries. Before the event (that is covered) and then after the event (this is what we are discussing now). So they have put in the journal number once in the first entry and now they want to add more information to that row.

Q3: No actually the 2 first numbers of the journal number is the year. And we will only have numbers 17, 18 and 19. If it makes it easier you can probably just look for a match in the suffix (the suffix could potentially start with a 0 - will have to check tomorrow when i go to work).

 

[Yongle]

What I trying to get you to tell me is
- what "string" will be delivered as search string to VBA?
- and what is the equivalent value in column C?

I think you are telling me that you want to find 19-1234567, but the column contains 191234567
If so then this should work for you

Regnskab = Sheets("Sheet2").Range("C:C").Find([COLOR=#ff0000]Replace(CVR.Value, "-", "")[/COLOR], LookAt:=xlWhole).Row

Let me know if I misunderstood

 

[pasjauo]

What I trying to get you to tell me is what "string" will be delivered as search string to VBA and what value is it expected to find a match against

I think you are telling me that you want to find 19-1234567, but the column contains 191234567
If so then this should work for you

Regnskab = Sheets("Sheet2").Range("C:C").Find([COLOR=#ff0000]Replace(CVR.Value, "-", "")[/COLOR], LookAt:=xlWhole).Row

Let me know if I have misunderstood

Ah okay. No the string will be 19-1234567 and NOT 191234567. When I test numbers with the - it gives me the cant fint the number error.

 

[Yongle]

If BOTH values look like this 19-1234567
then

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

If CVR is 19-1234567 and column C has 191234567
then

Regnskab = Sheets("Sheet2").Range("C:C").Find([COLOR=#ff0000]Replace(CVR.Value, "-", "")[/COLOR], LookAt:=xlWhole).Row

If CVR is 191234567 and column C has 19-1234567
then

 Regnskab = Sheets("Sheet2").Range("C:C").Find([COLOR=#ff0000]Left(CVR.Value, 2) & "-" & Right(CVR.Value, 7)[/COLOR],  LookAt:=xlWhole).Row