pasting from another list

[drrocket5292]

I have a list in column A on sheet1 and a list in column D of sheet2 with a whole bunch of information between the two. Next to the list on sheet2 is a date from the past 3-4 months. Is there a way in VBA to compare the list on sheet1 to the list on sheet2 and then paste the date from sheet2 to sheet1 column D if someone appears on both lists? i.e. if someone's SSN is in the list on sheet1 and in the list on sheet2 i want to copy the date next to their SSN on sheet2 and put it on the same row as their name on sheet1 but 3 columns over in column D.

 

[xlbo]

Utilise the code I gave you yesterday - the one using the FIND method

I suggested using something like

dim fCell as range

set fCell = Worksheets("X").Range("Y").Find(TheThing)

Being as fCell is actually a range, you can manipulate it like a range - including the use of the OFFSET method

To grab the data in the cell next to the found cell, use

fCell.offset(0,1).value

to set that to the cell in column D in the original worksheet, again, utilise the OFFSET method

As I recall, I suggested you loop through your 1st set of data using

For each c in range("RangeToLoopThru")
....process
next

in this case, c is also a range and so the OFFSET method can be applied - eg:

For each c in Range("TheRangeToLoopThru")
 set fCell = Sheets("SheetToLookIn").Range("RangeToLookIn").Find(What:=c.value)
  If not fCell is nothing then
    'item found
    c.entirerow.interior.colorindex = 48
    c.OFFSET(0,3).value = fCell.OFFSET(0,1).value
  else
    'item not found
  end if
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drrocket5292]

xlbo,
seriously, youre the best. thanks. one last question for you if you dont mind. if that date that i just inserted into the column is more than 80 days old i want to color that row turquoise instead of blue. i tried fooling around with the datediff function but couldnt get it to work. would that be the right way to do it? i.e.

if datediff("d",now,c.value) > 80 then
c.entirerow.interior.colorindex = 42

 

[PHV]

You may simply try this:
If Now - CDate(c.Value) > 80 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

couldn't have put it more succinctly myself !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drrocket5292]

what if my range in range("therangetoloopthru") in my code varies from week to week. instead of having a set range there, how could I tell excel to only loop through the cells with data in them?

 

[xlbo]

you need to dynamically set the range then.....

Instead of:

dim RangeToLoopThru as range
Set RangeToLoopThru = sheets("Sheet1").range("A2:A227")

you may use

dim RangeToLoopThru as range
dim LastRow as long
dim FirstRow as long

FirstRow = 2
LastRow = 227

Set RangeToLoopThru = sheets("Sheet1").range("A" & FirstRow & ":A" & LastRow & ")"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drrocket5292]

one last problem - ive got a couple of lines of code:

Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=colorindexofcell(R[1]C1,FALSE,TRUE)"
Range("AJ2").Select
Selection.Copy
Range("AJ2:AJ" & n).Select
ActiveSheet.Paste

that calls a function colorindexofcell but anywhere that i try to call that function in the sub it tells me argument not optional. what am i doing wrong?

 

[xlbo]

please post the code for that function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drrocket5292]

its actually 3 functions - I got it off the internet to allow me to sort by color and have used it for quite awhile and its always worked in the past but i cant seem to call it in my sub. what im trying to do is i created a batch file to open up a spreadsheet and then have code that colorcodes the spreadsheet based on the information inside and now i want to call this function so that i can automate the sheet to sort itself based on color before it saves and closes itself. i'm pretty new at all of this so i could just be making a stupid mistake when it comes to calling functions.


Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function


Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function

Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function

 

[xlbo]

When you call functions, you must give values for all arguments. I am guessing that one of them is not evaluating correctly - your best bet is to put a breakpoint on this line:

Range("AJ2").Select

and then use F8 to step through the code - this should allow you to see which particular line is causing the issue



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drrocket5292]

im still havign major difficulties with this - i get it to run without erroring out when i write the following line to call the function:

colorindexofcell range("$a2")

but then on my spreadsheet im getting the #name error

am i calling this function the wrong way? here is my complete code for this sub: any ideas what im doing wrong?

Sub copyandsort()

Dim ranger As Range
Dim cm As Range
Dim n As Integer
Dim x As Date
Dim my

x = Date
my = Format(x, "mmm d yyyy")

n = 0

Set ranger = Sheets("sheet1").Range("b1:b2000")
For Each cm In ranger
If cm.Value <> "" Then
n = n + 1
Else
End If
Next

ColorIndexOfCell Range("$A2")

Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=colorindexofcell(R[1]C1,FALSE,TRUE)"
Range("AJ2").Select
Selection.Copy
Range("AJ2:AJ" & n).Select
ActiveSheet.Paste

ActiveWorkbook.SaveAs Filename:= _
"X:\all six1 structures " & my & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


thanks for the help