Replacing vlookup with Macro

[Bodid]

I am in need of help. I have a worksheet that sometimes gets modified, meaning the number ofrows sometimes changed by other users. <o></o>
I have 2 sheets. Thefirst one is called “PPTT”. The secondone is “DateSheet”. Cell E1 of “PPTT”has a given date. I have a macro thatwill calculate dates 23 months prior to and after the date on “DateSheet” (i.eA-23 to A+23 with given date set to A-0).<o></o>
Here is where the problem comes in. Other users that add rows to “PPTT” sheet as neededand are not as proficient and do not understand the vlookup function. I was using the vlookup function to populatecolumn G based on the A-xx of Column E, both on the “PPTT” sheet. The vlookup would determine the “A-“ value incolumn E of “PPTT” sheet, find the corresponding “A-“ value in column A on the “DateSheet”and copy the date in column B on the “DateSheet” to column G of the “PPTT”sheet.<o></o>
I was hoping to get help with amacro that would do this for all rows, and eliminate the vlookup function. Any help is appreciated.<o></o>

 

[Llupo01]

Hello, can you pls post your current vlookup code?

 

[Bodid]

Hello, can you pls post your current vlookup code?

This was the code that I was trying to modify to match what I needed. It was a little more complex than anticipated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Dim ws2 As Worksheet
Dim ws13 As Worksheet
Dim i As Integer

Set ws2 = ActiveWorkbook.Sheets(2)
Set ws13 = ActiveWorkbook.Sheets(13)

With ws2

For i = 1 To 6
If ws2.Range("a" & i).Value = Target.Value Then
ws2.Range("b" & i, "w" & i).Copy
ws13.Range("b" & Target.Row).PasteSpecial
Application.CutCopyMode = False
Exit Sub
End If
Next i
End With
End If
End Sub

 

[Llupo01]

just post your original Vlookup formula, I will try to modify to VBA as you need

 

[Bodid]

just post your original Vlookup formula, I will try to modify to VBA as you need

Here is the original Vlookup formula.

=VLOOKUP(E3,DateSheet!$A$1:$B$47,2,FALSE)

Appreciate your help.

 

[Llupo01]

Thanks, pls try this...vlookup output will be placed in column G, starting in g1, going down, until cell in column E is not empty. Hope this will help you. If you want to run it automatically when any value in worksheet change and dont know how, just let me know. First pls test, if this works as you need.

sorry, I had to edit my post...found out, that some of the ranges were set incorrectly. Not it shloud be ok

Option Explicit

Sub vbaVlookup()

Dim lastrow As Long
Dim i As Integer

Application.ScreenUpdating = False
On Error Resume Next

lastrow = Cells(Rows.Count, "E").End(xlUp).Row

For i = 1 To lastrow

    If Cells(i, "E") <> Empty Then
    
    'below you can specify your ranges
    
   Cells(i, "G").Value = Application.WorksheetFunction _
    .VLookup(Cells(i, "E"), Sheets("datesheet").[a1:b47], 2, 0)
    ActiveCell.Offset(1, 0).Select

Else: Exit Sub

    End If

Next i

Application.ScreenUpdating = True

End Sub

 

[Bodid]

Thanks, pls try this...vlookup output will be placed in column G, starting in g1, going down, until cell in column E is not empty. Hope this will help you. If you want to run it automatically when any value in worksheet change and dont know how, just let me know. First pls test, if this works as you need.

sorry, I had to edit my post...found out, that some of the ranges were set incorrectly. Not it shloud be ok

Option Explicit

Sub vbaVlookup()

Dim lastrow As Long
Dim i As Integer

Application.ScreenUpdating = False
On Error Resume Next

lastrow = Cells(Rows.Count, "E").End(xlUp).Row

For i = 1 To lastrow

    If Cells(i, "E") <> Empty Then
    
    'below you can specify your ranges
    
   Cells(i, "G").Value = Application.WorksheetFunction _
    .VLookup(Cells(i, "E"), Sheets("datesheet").[a1:b47], 2, 0)
    ActiveCell.Offset(1, 0).Select

Else: Exit Sub

    End If

Next i

Application.ScreenUpdating = True

End Sub

It worked perfectly! Thank you very much!

 

[Llupo01]

i am happy to help my friend.

 

[Bodid]

Hi,

I was wondering if you could assist me with the coding that you helped me with before. As I added or deleted rows in my sheet, I have column B that I need to re-number. I inserted the code below into what we did before and it works, but I am not happy with calling out a definitive last row. I have not been able to successfully modify it by doing a count and then re-numbering. Can you assist with this?

Again, thank you for all you assistance,
Bodid


Dim StartNum As Integer
Dim FirstCell As Integer
Dim LastCell As Integer
StartNum = 1
FirstCell = 3
LastCell = 175
Application.EnableEvents = False
Do While FirstCell <= LastCell
Range("B" & FirstCell).Value = StartNum
FirstCell = FirstCell + 1
StartNum = StartNum + 1
Loop
Range("B" & LastCell + 1).Value = ""
Application.EnableEvents = True