I have a problem that I just can't seem to get my mind around. I know this should be easy, but I just don't what to use

The code I have pulls lots of data out of workbooks and consolidates it into several others. What I need it something that will for instance, look in column "A" for one for a value (this value is variable). If the value exists then select that row and set that row value to the variable "PasteRow". If the value doesn't exist in the row then select the last row + 1 and set this as the "PasteRow"

Basically I need to have something automatically replace data if it has been updated and if not then add the new data. If the variable value exists in the first column then the data has been updated, so select the row and then 'do stuff' and then continue on to the next set of data. If the value doesn't exist then this is new data and paste it to the bottom

I know this can be done, and I have learned alot in the last year or so, esp. with the help of this newsgroup. I just need to get some direction on this, as I can't figure it out

Thanks for you time and effor

Jim

Try this
Sub GetRow(Val As String
Dim PasteRange As Range, cnt As Singl
'On Error Resume Nex
Set x = Cells.Find(Val
If x Is Nothing The
cnt = ActiveSheet.UsedRange.Rows.Count +
Set PasteRange = Rows(cnt
Exit Su
End I
Set PasteRange = Range(x.Address).EntireRo
Set x = Nothin
End Sub
----- James Stephens wrote: ----

I have a problem that I just can't seem to get my mind around. I know this should be easy, but I just don't what to use

The code I have pulls lots of data out of workbooks and consolidates it into several others. What I need it something that will for instance, look in column "A" for one for a value (this value is variable). If the value exists then select that row and set that row value to the variable "PasteRow". If the value doesn't exist in the row then select the last row + 1 and set this as the "PasteRow"

Basically I need to have something automatically replace data if it has been updated and if not then add the new data. If the variable value exists in the first column then the data has been updated, so select the row and then 'do stuff' and then continue on to the next set of data. If the value doesn't exist then this is new data and paste it to the bottom

I know this can be done, and I have learned alot in the last year or so, esp. with the help of this newsgroup. I just need to get some direction on this, as I can't figure it out

Thanks for you time and effor

Jim

This looks like it almost works, I tried it out and I have a couple of questions

Now how do I select which column I want this to search in
Do I just start it with - columns("A:A").Selec

Also when I try to use the "PasteRange", I have an error. I use this "PasteRange", value for other formulas, such as - Range("B" & PasteRange).Select and then do stuff. Is there a way to set this paste range as just the row number and not an actual cell value, I think that is the problem I am running into, however I am not sure

Thanks for your help

Ji

----- chris wrote: ----

Try this
Sub GetRow(Val As String
Dim PasteRange As Range, cnt As Singl
'On Error Resume Nex
Set x = Cells.Find(Val
If x Is Nothing The
cnt = ActiveSheet.UsedRange.Rows.Count +
Set PasteRange = Rows(cnt
Exit Su
End I
Set PasteRange = Range(x.Address).EntireRo
Set x = Nothin
End Sub
----- James Stephens wrote: ----

I have a problem that I just can't seem to get my mind around. I know this should be easy, but I just don't what to use

The code I have pulls lots of data out of workbooks and consolidates it into several others. What I need it something that will for instance, look in column "A" for one for a value (this value is variable). If the value exists then select that row and set that row value to the variable "PasteRow". If the value doesn't exist in the row then select the last row + 1 and set this as the "PasteRow"

Basically I need to have something automatically replace data if it has been updated and if not then add the new data. If the variable value exists in the first column then the data has been updated, so select the row and then 'do stuff' and then continue on to the next set of data. If the value doesn't exist then this is new data and paste it to the bottom

I know this can be done, and I have learned alot in the last year or so, esp. with the help of this newsgroup. I just need to get some direction on this, as I can't figure it out

Thanks for you time and effor

Jim