Is Indexing Possible in VBA/Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

I'm having trouble with an Excel/VBA problem. I've created a spreadsheet which has a User Interface created in VBA.

I want the user to select 3 fields (on the form) which are unique each time. The fields are:

Station
Year
Period

There can't be duplicate combinations of these values. I know how to do this in Access, but don't want to re-program everythin got operate there.

Can anyone suggest how this is done in Excel with VBA? I feel like I've been going round in circles, so any help would be greatly appreciated!

Thanks,
Stan
 
Stan,

As there is a user interface via the form, I assume you are saving the
values?

One way to test would be to use the worksheetfunction COUNTIF to see if the
value already exists.. For instance, assume that you save these 3 fields in
columns A, B, C in a worksheet, you then use code like this to test

Dim cRows As Long

With Worksheets("Sheet1")
cRows = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("D1").Formula = "=A1&B1&C1"
.Range("D1").AutoFill Destination:=.Range("D1:D" & cRows)
If WorksheetFunction.CountIf(.Range("D1:D" & cRows), "abc") > 0 Then
MsgBox "Already used"
End If
End With

Change the value abc for the concatenated values just entered. You could
even create the concatenated value in column D as you save the 3 values,
avoiding the autofill.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Stan said:
Hello!

I'm having trouble with an Excel/VBA problem. I've created a spreadsheet
Click to expand...
which has a User Interface created in VBA.
I want the user to select 3 fields (on the form) which are unique each time. The fields are:

Station
Year
Period

There can't be duplicate combinations of these values. I know how to do
Click to expand...
this in Access, but don't want to re-program everythin got operate there.
Can anyone suggest how this is done in Excel with VBA? I feel like I've
Click to expand...
been going round in circles, so any help would be greatly appreciated!
 
Cheers Bob, that's great.

I'll give it a bash and should i have any more probs, I'll be back here!

Thanks again!

S
 
Back
Top