Is Indexing Possible in VBA/Excel?
 

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 a

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

Is Indexing Possible in VBA/Excel?
 

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" wrote in message
...
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 a

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

Is Indexing Possible in VBA/Excel?
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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 a

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