CODEWITHSUNDEEP
vbaexcel
Mikz
Mikz

Reputation: 591

VBA , Using offset

I am having 4 sht, sht1, sht2, sht3 and sht4.

I am copying the columns E and F from sht 1 to sht3. and then i look into the corresponding values in sht 2, and paste them in sht3.

I then lookinto in my sht3, if the column "G" has "NO"; then i copy the corresponding rows to sht4.

till, this i have completed coding.

I wanted to look into the column E in sht4, and paste the corresponding ID from sht1. Could someone tell, how i could do it ?

EDIT.

In sht3, i have the rows filled only when there is Id in column F.

In few cases, i dont have column F,means there is no ID.

so, i copy them to sht4. Now i have in sht4, column E Filled. I want to look into the relevant Information of those ID in sht1. I want the Information from each and every column in sht1, except E .

I know we can use Offset, but how do I use it in this case, I have tried the following code

Sub nlookup()
Dim i As Long
Dim totalrows As Long
Dim rng As Range
Sheets("sht1").Select
totalrows = ActiveSheet.UsedRange.Rows.Count
Sheets("sht4").Select
For i = 5 To totalrows
Set rng = Sheets("sht2").UsedRange.Find(Cells(i, 5).Value)
'If it is found put its value on the destination sheet
If Not rng Is Nothing Then
Cells(i, 6).Value = rng.Value
Cells(i, 1).Value = rng.Offset(0, 0).Value
Cells(i, 2).Value = rng.Offset(0, 14).Value
Cells(i, 3).Value = rng.Offset(0, 1).Value
Cells(i, 4).Value = rng.Offset(0, 2).Value
Cells(i, 12).Value = rng.Offset(0, 8).Value
Cells(i, 13).Value = rng.Offset(0, 9).Value
End If
Next
End Sub

Set rng = Sheets("sht2").UsedRange.Find(Cells(i, 5).Value), there is no Need of looking into this line, i beleive.

Image of sht4. which is extracted from sht3. Image of sht1

Upvotes: 0

Views: 157

Answers (2)

Mrig
Mrig

Reputation: 11702

The code will consider the following as discussed in chat:

  1. Data should be copied from sht1 to sht4 on Id's in both sheets
  2. Id's are in Column L and Column E for sht1 and sht4 respectively
  3. Columns to be copy from sht1 to sht4 as A->A, B->C,C->D,I->L,J->M,O->B

  4. Data in sht1 and sht4 starts from Row 5 and Row 2 respectively

    Sub Demo()
    Dim srcLastRow As Long, destLastRow As Long
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim i As Long, j As Long

    Application.ScreenUpdating = False

    Set srcWS = ThisWorkbook.Sheets("Sht1")
    Set destWS = ThisWorkbook.Sheets("Sht4")
    srcLastRow = srcWS.Cells(srcWS.Rows.Count, "L").End(xlUp).Row
    destLastRow = destWS.Cells(destWS.Rows.Count, "E").End(xlUp).Row

    For i = 2 To destLastRow
        For j = 5 To srcLastRow
            If destWS.Cells(i, "E").Value = srcWS.Cells(j, "L").Value Then
                destWS.Cells(i, "A") = srcWS.Cells(j, "A")
                destWS.Cells(i, "B") = srcWS.Cells(j, "O")
                destWS.Cells(i, "C") = srcWS.Cells(j, "B")
                destWS.Cells(i, "D") = srcWS.Cells(j, "C")
                destWS.Cells(i, "L") = srcWS.Cells(j, "I")
                destWS.Cells(i, "M") = srcWS.Cells(j, "J")
            End If
        Next j
    Next i

    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Plagon
Plagon

Reputation: 3139

Why not just use Cells(i, 4).Value = rng.Cells(i, 6).Value ? Also get rid of the.Select

Sub nlookup()
dim sht as Worksheet
Dim i As Long
Dim totalrows As Long
Dim rng As Range

totalrows = Sheets("sht1").UsedRange.Rows.Count
Set sht = Worksheets("sht4")

For i = 5 To totalrows
Set rng = Sheets("sht2").UsedRange.Find(sht.Cells(i, 5).Value)
'If it is found put its value on the destination sheet
If Not rng Is Nothing Then
sht.Cells(i, 6).Value = rng.Value
sht.Cells(i, 1).Value = rng.Cells(i, 1).Value
sht.Cells(i, 2).Value = rng.Cells(i, 16).Value
sht.Cells(i, 3).Value = rng.Cells(i, 4).Value
sht.Cells(i, 4).Value = rng.Cells(i, 6).Value
sht.Cells(i, 12).Value = rng.Cells(i, 20).Value
sht.Cells(i, 13).Value = rng.Cells(i, 22).Value
End If
Next
End Sub

Upvotes: 0

Related Questions