VBA ifelse with offset

[TravisVBA]

Hello, still relatively new to vba and working on creating a macro that updates expense rates based on a certain qualifier entered. I wonder if any wizards here could take a gander and let me know what wording I need to change for this to work properly as I'm yet able to find anything readily detailing what I want to do. I should clarify the offset feature is pulling info from formulas that are imputed by the user.

Sub Bevel1_Click()
If Workbook("rate update").Sheets("sheet1").Range("B1").Value = 10 Then
Workbook("rate update").Sheets("rates").Range("j4:J166").Select Copy
Workbook("rate update").Sheets("sheet1").Range("B3").Select
PasteSpecial
Workbook("rate update").Sheets("rates").Range("A4:A166").Select Copy
Workbook("rate update").Sheets("sheet1").Range("B2").Select
PasteSpecial
ElseIf Workbook("rate update").Sheets("sheet1").Range("B1").Value = 20 Then
Workbook("rate update").Sheets("rates").Range("b4:b166").Select Copy
Workbook("rate update").Sheets("sheet1").Range("B3").Select
PasteSpecial
Workbook("rate update").Sheets("rates").Range("A4:A166").Select Copy
Workbook("rate update").Sheets("sheet1").Range("B2").Select
PasteSpecial
Else: MsgBox ("please enter valid qualifier")
End If
'offseting vlookup info one cell to the left if a value exists
For Each cell In Workbook("rate update").Sheets("sheet1").range("B10:B20").value > 0 then cell.offset.value(0,-1)
Next
If Workbook("rate update").Sheets("sheet1").Range("B1").Value = 10 Then
Workbook("rate update").Sheets("sheet1").Range("B3:b155").Select Copy
Workbook("rate update").Sheets("rates").Range("j4").Select
PasteSpecial
ElseIf Workbook("rate update").Sheets("sheet1").Range("B1").Value = 20 Then
Workbook("rate update").Sheets("sheet1").Range("B3:b155").Select Copy
Workbook("rate update").Sheets("rates").Range("b4").Select
PasteSpecial

Else: MsgBox ("please enter valid qualifier")

End If
Workbook("rate update").Sheets("sheet1").Select
MsgBox ("update complete")



End Sub

Any advise/edits would be appreciated and I'm sure I could build my knowledge basis off of.

-Travis

 

[RMS123]

Cleaned up a bit and edited:

Sub Bevel1_Click()
Dim Ratewb As Workbook
Dim Ratews As Worksheet
Dim ws As Worksheet
Dim cell As Range


Set Ratewb = Workbook("rate update")
Set Ratews = Ratewb.Sheets("rates")
Set ws = Ratewb.Sheets("sheet1")


    If ws.Range("B1").Value = 10 Then
    
        Ratews.Range("j4:J166").Copy
        ws.Range("B3").PasteSpecial
        Ratews.Range("A4:A166").Copy
        ws.Range("B2").PasteSpecial
        
        Application.CutCopyMode = False
        
    ElseIf ws.Range("B1").Value = 20 Then
    
        Ratews.Range("b4:b166").Copy
        ws.Range("B3").PasteSpecial
        Ratews.Range("A4:A166").Copy
        ws.Range("B2").PasteSpecial
        
        Application.CutCopyMode = False
        
    Else: MsgBox ("please enter valid qualifier")


    End If
    
    'offseting vlookup info one cell to the left if a value exists
    For Each cell In ws.Range("B10:B20")


        If cell.Value > 0 Then cell.Offset(0, -1).Value = cell.Value
    
    Next cell
    
    If ws.Range("B1").Value = 10 Then
    
        ws.Range("B3:b155").Copy
        Ratews.Range("j4").PasteSpecial
        
        Application.CutCopyMode = False
        
    ElseIf ws.Range("B1").Value = 20 Then
    
        ws.Range("B3:b155").Copy
        Ratews.Range("b4").PasteSpecial
        
        Application.CutCopyMode = False


    Else: MsgBox ("please enter valid qualifier")


    End If


    ws.Select
    MsgBox ("update complete")






End Sub

Regards.