VBA Help with Value

[fari1]

Hi,
in my sheet1, in any of the cells of this sheet there's a word i-e Description and i want to copy the value below this cell, it can be next to the cell description, or two cells down, or three, meaning the first value after this word description.
E.g

Case1
Description
 
2312763
 
Case 2
Description
 
 
 
342867
 
Case3
Description
 
 
 
 
 
 
 
3428729

 

[Rick Rothstein]

You didn't say where you wanted to copy this value to, so I have given you code to obtain that value... you can then do whatever you want with it.

  Dim ValueBelowDescription As Variant, DescriptionCell As Range, CellBelowDescription As Range
  .....
  .....
  Set DescriptionCell = Worksheets("Sheet1").Cells.Find("Description", , xlValues, xlWhole, , , False)
  Set CellBelowDescription = Cells.Find("*", DescriptionCell, xlValues, xlWhole, xlByColumns, xlNext)
  ValueBelowDescription = CellBelowDescription.Value

 

[fari1]

hi,
this code is not finding anything and not copying, i've added paste line into it

it is pasting in sheet2 the last copied value, rathr than the value under description from sheet1

 

[Rick Rothstein]

Check your "empty" cells and make sure there is nothing in them... when I copied your data from the forum into my worksheet, the supposed empty cells had a blank (space) character in them... I don't know if that character came from your actual data or were some kind of artifact from the forum. If they are actually in your cells, you should probably remove them... doing so should then make my code work for you.

 

[fari1]

there are empty cells, between description and the next text, meaning it is totally dynamic, the next text can be right after description or after blank cells, you have to give a code, which gives the next text whereevr it is after description

 

[fari1]

and i've tried it even right after the description, still not working

 

[fari1]

i've made a few changes, want to make it work this way, i want it to copy data right after the used cell in sheet data, but it's not working

Sub newcode()
Dim ValueBelowD As Variant, DCell As Range, CellBelowD As Range
  Set DCell = Worksheets("sheet1").Cells.Find("Document", , xlValues, xlWhole, , , False)
  Set CellBelowD = Cells.Find("*", DCell, xlValues, xlWhole, xlByColumns, xlNext)
  ValueBelowD = CellBelowD.Value
  With Sheets("data")
  .Range("A1").Value = CellBelowD
  .Offset(, 1).Value = Sheets("data").Range("A1").Value
  End With
End Sub

 

[Rick Rothstein]

The code I posted works here on my sample data, so I'm not sure what is different about your set up. Can you post your workbook to one of the free file sharing website on the Internet so we can download it in order to work with your actual set up?

If so, you can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

If you are uncomfortable about doing that, you can send the file directly to me if you want. My email address is rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbols they spell out).

 

[fari1]

extremely Sorry for being late, got trapped in work, will send u file on your email id just in a while

 

[Rick Rothstein]

  Set CellBelowD = Cells.Find("*", DCell, xlValues, xlWhole, xlByColumns, xlNext)

I think the problem you are having is due to some sloppiness on my part. I forgot to qualify the above range with its worksheet (like I did with the line above it), so if you ran this code from any sheet other than Sheet1, the wrong cell would be set. Change the above line to this...

  Set CellBelowD = Worksheets("Sheet1").Cells.Find("*", DCell, xlValues, xlWhole, xlByColumns, xlNext)

With Sheets("data")
  .Range("A1").Value = CellBelowD
  [B][COLOR=darkred].Offset(, 1).Value = Sheets("data").Range("A1").Value[/COLOR][/B]
End With

I think there is a problem with the line I highlighed in red. The expression to the left of the equal sign is shown as an Offset from the object of the With statement (that is what the dot is doing); however that object is a worksheet... you cannot offset one column from a sheet... you must do it from a range. Given the construction of that line, I'm guessing that range would be a cell someplace; however, I am not entirely sure which cell you would want to reference. Can you clarify that for us? I also just want to point out that since the reference to the right of the equal sign is the same as the object of the With statement, you do not have to repeat it; so the expression to the right of the equal sign could be written like this...

 = .Range("A1").Value