PDA

View Full Version : [SOLVED] VBA Macro to select and bring back a new campaign code



digital786
03-12-2018, 09:53 AM
Hi Everyone Hope your all well.

Hopefully someone can help me with this, am trying to use VBA macro to help me generate a unique campaign code used in emails but i been stuck with it for a while now.

I have 2 sheets.

Sheet 1 (Campaign Codes) is where the unique ID code is going to go in cell 'D4'
Sheet 2 (Config) is where a list of all the campaign codes are held. column 'H2' down to 'H3000'

the campaign codes would be listed in numerical order for example campaign0001, campaign 0002, campaign0003



I have a button set in sheet 1 which i will assign the macro to.


Working example:

I press the macro button
Macro runs and brings back ONLY 1 unique E-code EACH TIME from the list in sheet 2, which has not been used before and inserts it into Sheet 1 (Campaign Codes) cell 'D4'
In the logic also to highlight the cell in sheet 2 of the unique E-Code which has been used in red and the rest remain in green. So when it comes to clicking the macro button it looks for cell in red and green back campaign code for the cell below in green.


I would be extremely great full if someone could help me with this. I've attached the document i am working on.

21807

SamT
03-12-2018, 10:28 AM
Something like this:
Sub Button_Click()
Dim Cel As Range

Application.FindFormat.Interior.ColorIndex = 4

Set Cel = Sheets("Config").Range("H:H").Find(What:="*", _
After:=Range("H1"), SearchFormat:=True)

If Cel Is Nothing Then
MsgBox "No unused Campaign Codes available."
'Exit Sub
End If

Cel.Interior.ColorIndex = 3
Sheets("Campaign Codes").Range("D4") = Cel

End Sub

NoSparks
03-12-2018, 10:35 AM
maybe this:


Sub Rectangle1_Click()
Dim cel As Range
For Each cel In Sheets("Config").Range("H2:H3000")
If cel.Interior.ColorIndex <> 3 Then
Sheets("Campaign Codes").Range("D4").Value = cel.Value
cel.Interior.ColorIndex = 3
Exit For
End If
Next cel
End Sub

SamT
03-12-2018, 04:58 PM
Using For Each on 3000 Cells takes 1000 times as long as on 3 cells

Using Find on 3000 cells take the same time as on 3 cells.

NoSparks
03-12-2018, 08:37 PM
I agree but at least it works.

Isn't color index 4 = RGB(0,255,0) ?

OP's cells are RGB(198,224,180)

digital786
03-13-2018, 01:51 AM
Hi Guys

SamT thank you very much for reply back to my query i tried you VBA code but unfortunately it didn't work. But thank you NoSparks your VBA code was perfect its working like a charm

I can happily say this has been resolved.