0

I am new to VBA and excel macro, I want to create a macro which will search for text in Column and if found will copy corresponding value from column B to another workbook.

Example: Macro will search for fixed "TextA3" from column A and if found will copy value from B3 (which is TextB3 in below example)and will paste to another workbook.

TextA1  TextB1 
TextA2  TextB2 
TextA3  TextB3 
TextA4  TextB4 
TextA5  TextB5
TextA6  TextB6 
TextA7  TextB7

Tried searching this website but unable to figure out how to copy adjacent cell.

I am working with below code:

Sub Luxation2()
Dim K As Long, r As Range, v As Variant
K = 1
Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("raw data")
Set w2 = Sheets("data manipulation")
w1.Activate
For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
    v = r.Value
    If InStr(v, "Ticket No. ") > 0 Then
        r.Copy w2.Cells(K, 1)
        K = K + 1
    End If
Next r
End Sub

Many thanks! in advance.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Bali
  • 1
  • 1
  • 2
  • The link that I posted shows how to copy the entire line. You can easily modify it to copy the value from the next cell. – Siddharth Rout Sep 13 '14 at 08:45
  • Hi, already checked tht post but unable to modify it to search for a string, say my search ends in A5 then I need to copy B5 to another sheet. Can u please help. – Bali Sep 13 '14 at 09:33
  • Can you update your question with the exact code that you are using after modifying it? Don't just paste the exact code from there :P – Siddharth Rout Sep 13 '14 at 09:44
  • @siddharth-rout This post is different from the one you listed as a duplicate. He's asking how to copy an adjacent cell from the one being evaluated to a different worksheet destination, not how to copy the active row. There is a difference. – Kevin Francis Sep 13 '14 at 11:47
  • @RonRosenfeld I haven't posted any code. The code was posted by Bali. I think the key here was how to copy the adjacent cell. I have a solution ready, but we're not suppose to post answers in the comments. – Kevin Francis Sep 13 '14 at 14:06
  • One solution would be...Sub Luxation2() Dim r As Range Dim K As Long Dim v As Variant Dim w1 As Worksheet Dim w2 As Worksheet K = 1 Set w1 = Sheets("raw data") Set w2 = Sheets("data manipulation") For Each r In w1.UsedRange.Columns(1).Cells ActiveSheet.UsedRange v = r.Value Debug.Print ActiveCell.Address If InStr(v, "Ticket No. ") > 0 Then Cells(r.Row, 2).Copy (w2.Cells(K, 1)) K = K + 1 End If Next End Sub – Kevin Francis Sep 13 '14 at 14:07
  • @KevinFrancis Sorry, got tagged to the wrong person, not sure why. I'll rewrite it to Bali. – Ron Rosenfeld Sep 13 '14 at 14:29
  • @Bali There is nothing in the code you posted referencing another work**book**. Is that what you mean? Or do you mean another work**sheet**?. To reference the adjacent cell in your code, you can probably just use r(1,2). So the code might look like r(1,2).copy [Destination] – Ron Rosenfeld Sep 13 '14 at 14:29
  • @KevinFrancis. Not that post is not to copy the "Active Row". THat link shows you how to copy all the rows for a particular text. You have to simply modify that code so that instead of the rows, it copies the adjacent text. The approach is the same for this question as well. :) – Siddharth Rout Sep 14 '14 at 05:29
  • @siddharth-rout I agree, it is very similar. It just depends on how granularity one wants to dive into details to consider a question a duplicate. I felt Bali's question was how to determine the adjacent cell for the one being evaluated, which is different then determining the same row of the cell being evaluated. While you and I would have no problem seeing the commonality, a new user with reputation of 1 may not see it. If he did a search on Cell instead of Row, your answer wouldn't have shown up. Doesn't hurt to answer this for search results. But I do see your point as well. :-) – Kevin Francis Sep 14 '14 at 05:52
  • @KevinFrancis: If community feels this is not a duplicate question, they have every right to open it. In fact I will post an answer using that approach :D – Siddharth Rout Sep 14 '14 at 06:50

1 Answers1

0

I myself am new to Excel-VBA but I guess since there is no answer yet I'll try to answer.

I am not entirely sure if I fully comprehend your problem. What I understood was that you want to copy a cell next to the cell you have selected.

You could try to use

w2.cells(K, 1).offset(Column, Row)

I hope I could help and it is not entirely wrong, otherwise please correct me :)

NiceRice
  • 29
  • 6