A Tale of Two Cities- Find and add
 

Hi,

I am trying to write a program a macro that will help me accomplish a
find and add. Please forgive me if this is too complex of a post but
I am new at programming so I hope someone might be able to help me out

I have a column entitled color and it lists the colors of cars on
specific lots.

[Lot] [Color]
214 3ax Red
214 3ax Green
214 3yb Yellow
386 3ax Blue
386 3yb Gray
386 2xb Yellow

My problem is that the letters and numbers in front of the color may
change, but somewhere in the color column there will always be the
color name (i.e. Red).

I have a predetermined list of colors (there are many different kinds
of colors) and I am trying to write a macro that scans the color
column for each lot and if finds that a specific color keyword is
missing it will add a row to the end of the lot and add the missing
color name into it.

So if the macro went through all the records in lot 214 and saw that
the color gray was missing it would go to the end of that section and
add a row and put the word "gray" in the second column of the new row.

I know this is a little complex, but any assistance that any one could
offer would be highly appreciated.

Sincerely,
Rambo

A Tale of Two Cities- Find and add
 

Sub getcolor()

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Set ColorRange = Range(Cells(1, "B"), Cells(LastRow, "B"))


For Each cell In ColorRange

'get color from string
MyColor = Mid(cell, InStr(cell, " ") + 1)

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set SearchRange = Range(Cells(1, "D"), _
Cells(LastRow, "D"))

Set c = SearchRange.Find(what:=MyColor, LookIn:=xlValues)

If c Is Nothing Then
If (LastRow = 1) And IsEmpty(Cells(1, "D")) Then
Cells(1, "D").Value = MyColor
Else
Cells(LastRow + 1, "D").Value = MyColor
End If
End If

Next cell

"Rambo" wrote:

Hi,

I am trying to write a program a macro that will help me accomplish a
find and add. Please forgive me if this is too complex of a post but
I am new at programming so I hope someone might be able to help me out

I have a column entitled color and it lists the colors of cars on
specific lots.

[Lot] [Color]
214 3ax Red
214 3ax Green
214 3yb Yellow
386 3ax Blue
386 3yb Gray
386 2xb Yellow

My problem is that the letters and numbers in front of the color may
change, but somewhere in the color column there will always be the
color name (i.e. Red).

I have a predetermined list of colors (there are many different kinds
of colors) and I am trying to write a macro that scans the color
column for each lot and if finds that a specific color keyword is
missing it will add a row to the end of the lot and add the missing
color name into it.

So if the macro went through all the records in lot 214 and saw that
the color gray was missing it would go to the end of that section and
add a row and put the word "gray" in the second column of the new row.

I know this is a little complex, but any assistance that any one could
offer would be highly appreciated.

Sincerely,
Rambo

A Tale of Two Cities- Find and add
 

On Aug 1, 3:58 pm, Joel wrote:
Sub getcolor()

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Set ColorRange = Range(Cells(1, "B"), Cells(LastRow, "B"))

For Each cell In ColorRange

'get color from string
MyColor = Mid(cell, InStr(cell, " ") + 1)

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set SearchRange = Range(Cells(1, "D"), _
Cells(LastRow, "D"))

Set c = SearchRange.Find(what:=MyColor, LookIn:=xlValues)

If c Is Nothing Then
If (LastRow = 1) And IsEmpty(Cells(1, "D")) Then
Cells(1, "D").Value = MyColor
Else
Cells(LastRow + 1, "D").Value = MyColor
End If
End If

Next cell



"Rambo" wrote:
Hi,

I am trying to write a program a macro that will help me accomplish a
find and add. Please forgive me if this is too complex of a post but
I am new at programming so I hope someone might be able to help me out

I have a column entitled color and it lists the colors of cars on
specific lots.

[Lot] [Color]
214 3ax Red
214 3ax Green
214 3yb Yellow
386 3ax Blue
386 3yb Gray
386 2xb Yellow

My problem is that the letters and numbers in front of the color may
change, but somewhere in the color column there will always be the
color name (i.e. Red).

I have a predetermined list of colors (there are many different kinds
of colors) and I am trying to write a macro that scans the color
column for each lot and if finds that a specific color keyword is
missing it will add a row to the end of the lot and add the missing
color name into it.

So if the macro went through all the records in lot 214 and saw that
the color gray was missing it would go to the end of that section and
add a row and put the word "gray" in the second column of the new row.

I know this is a little complex, but any assistance that any one could
offer would be highly appreciated.

Sincerely,
Rambo- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick reply. I am trying to learn the basics of coding
so I was wondering if you might be able to give me a general
description of what is going on in the code.

Sincerely,
Rambo

A Tale of Two Cities- Find and add
 

Sub getcolor()

Rows.count is a constant indicating last row of worksheet 65535
End - xlup say to go to last row and find first row with data
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Using Row b, set the range like "B1:B25" , b25 being last row
Set ColorRange = Range(Cells(1, "B"), Cells(LastRow, "B"))

loops through each cell in ColorRange
For Each cell In ColorRange


Find the blank character in the line and then extract from +1 from the blank
to the end of the string
'get color from string
MyColor = Mid(cell, InStr(cell, " ") + 1)


finds Last row in column D like above
LastRow = Cells(Rows.Count, "D").End(xlUp).Row

Set up another range like above but in column D
Set SearchRange = Range(Cells(1, "D"), _
Cells(LastRow, "D"))

Searchs tthe Column D range for the color extracted above
Set c = SearchRange.Find(what:=MyColor, LookIn:=xlValues)


check if it found the color
If c Is Nothing Then

Last row will return a 1 if the column is empty. One will also be return if
there is only one row of data. This code makes sure the data is entered in
row 1 and doesn't over-write previous data

Otherwise, it write to the first empty row (Lastrow + 1)
If (LastRow = 1) And IsEmpty(Cells(1, "D")) Then
Cells(1, "D").Value = MyColor
Else
Cells(LastRow + 1, "D").Value = MyColor
End If
End If
---------------------------------------------------------------------
Next cell

"Rambo" wrote:

On Aug 1, 3:58 pm, Joel wrote:
Sub getcolor()

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

Set ColorRange = Range(Cells(1, "B"), Cells(LastRow, "B"))

For Each cell In ColorRange

'get color from string
MyColor = Mid(cell, InStr(cell, " ") + 1)

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set SearchRange = Range(Cells(1, "D"), _
Cells(LastRow, "D"))

Set c = SearchRange.Find(what:=MyColor, LookIn:=xlValues)

If c Is Nothing Then
If (LastRow = 1) And IsEmpty(Cells(1, "D")) Then
Cells(1, "D").Value = MyColor
Else
Cells(LastRow + 1, "D").Value = MyColor
End If
End If

Next cell



"Rambo" wrote:
Hi,

I am trying to write a program a macro that will help me accomplish a
find and add. Please forgive me if this is too complex of a post but
I am new at programming so I hope someone might be able to help me out

I have a column entitled color and it lists the colors of cars on
specific lots.

[Lot] [Color]
214 3ax Red
214 3ax Green
214 3yb Yellow
386 3ax Blue
386 3yb Gray
386 2xb Yellow

My problem is that the letters and numbers in front of the color may
change, but somewhere in the color column there will always be the
color name (i.e. Red).

I have a predetermined list of colors (there are many different kinds
of colors) and I am trying to write a macro that scans the color
column for each lot and if finds that a specific color keyword is
missing it will add a row to the end of the lot and add the missing
color name into it.

So if the macro went through all the records in lot 214 and saw that
the color gray was missing it would go to the end of that section and
add a row and put the word "gray" in the second column of the new row.

I know this is a little complex, but any assistance that any one could
offer would be highly appreciated.

Sincerely,
Rambo- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick reply. I am trying to learn the basics of coding
so I was wondering if you might be able to give me a general
description of what is going on in the code.

Sincerely,
Rambo