Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

If statement part wont run what did I do wrong?

0
Sub GetData()
Sheets("shippingpapers").Range("B3").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("P3").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("B29").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("P29").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("B45").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("P45").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("B61").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("P61").Value = Sheets("inputform").Range("F4").Value
Sheets("shippingpapers").Range("F3").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("T3").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("F29").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("T29").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("F45").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("T45").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("F61").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("T61").Value = Sheets("inputform").Range("F6").Value
Sheets("shippingpapers").Range("B5").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("P5").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("B31").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("P31").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("B47").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("P47").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("B63").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("P63").Value = Sheets("inputform").Range("F8").Value
Sheets("shippingpapers").Range("F5").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("T5").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("F31").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("T31").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("F47").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("T47").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("F63").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("T63").Value = Sheets("inputform").Range("F10").Value
Sheets("shippingpapers").Range("B7").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("P7").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("B33").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("P33").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("B49").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("P49").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("B65").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("P65").Value = Sheets("inputform").Range("F14").Value
Sheets("shippingpapers").Range("B12").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("P12").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("B38").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("P38").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("B54").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("P54").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("B70").Value = Sheets("inputform").Range("F19").Value
Sheets("shippingpapers").Range("P70").Value = Sheets("inputform").Range("F19").Value
    If inputform.Range("f12").Value = "Ground" Then
    shippingpapers.Range("K4") = "XXXXX"
End If
End Sub
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Tags: Tags were updated to reflect the topic of the question.
Answer
Discuss

Discussion

Welcome to the forum. HTML code tags consist of CODE and /CODE, each in square brackets, to mark the beginning of code and the end. HTML will pick out whatever is in between and display it differently from the surrounding text.
You can insert a pair of code tags by clicking where you clicked and then paste your code in place of the words "Your code goes here", which is between the tags. You can also first paste the cost, select it and then click the "CODE" button to let the site's program atdd the code tags to the selected area.
Variatus (rep: 4879) Dec 6, '20 at 9:33 pm
I added the CODE tags for you - seems like you now know how to use it so no worries) If you don't need your other duplicate question now, you can go ahead and delete it and leave this one so its easier to find the answer that best helped you in the future.
don (rep: 1979) Dec 7, '20 at 12:01 am
Add to Discussion

Answers

0

Here is your code stramlined a little. I have added comments to show how you can avoid endless repetitions. Another way would be to construct loops.

Sub GetData()

    ' declare your variables
    Dim WsInput         As Worksheet            ' Worksheets("Inputform")
    Dim WsDocs          As Worksheet            ' Worksheets("shippingpapers")
    Dim Rng             As Range

    ' assign objects to the declared variables
    Set WsInput = Worksheets("Inputform")
    Set WsDocs = Worksheets("shippingpapers")

    ' start a "With" block, terminated by "End With"
    ' within the block all expressions starting with a period refer to
    ' the object specified in the With statement
    ' Careful! Where the leading period is omitted the expression refers
    '          to the ActiveSheet. Range("A3") is different from .Range("A3")
    With WsInput
        Set Rng = WsDocs.Range("B3, P3, B29, P29, B45, P45, B61, P61")
        Rng.Value = .Range("F4").Value
        Rng.Offset(, 4).Value = .Range("F6").Value
        Rng.Offset(2).Value = .Range("F8").Value
        Rng.Offset(2, 4).Value = .Range("F10").Value
        Rng.Offset(4).Value = .Range("F8").Value
        Rng.Offset(4).Value = .Range("F14").Value
        Rng.Offset(9).Value = .Range("F19").Value
    End With


    ' as a broad rule,
    ' use syntax for addressing cells to address cells
    ' cells are addressed by their coordinates (numbers)
    ' the syntax is Ws.Cells([Row number], [Column number or ID])
    ' use syntax for addressing ranges only to address ranges
    ' ranges are addressed by their names (text strings)
    ' the syntax is Ws.Range("Range name")
    ' in the absence of given names, Excel's VBA concatenates
    ' range names from cell coordinates
    If WsInput.Cells(12, "F").Value = "Ground" Then
        WsDocs.Cells(4, "K").Value = "XXXXX"
    End If
End Sub

Your IF styatement didn't work because Excel didn't recognize your syntax for addressing worksheets. It really helps if you declare your objects before you start using them. Microsoft, in their constant effort to make things easier, started an effort (by means of VBE's macro recorder) to conflate Sheets and Worksheets. These are two different collections. It really makes no material difference which one you use - till the day it does make a difference. According to MS (presumed) statistics 98% of all programmers will never reach that day, 1% will know the difference by then, and the other 1% aren't important (to Microsoft). I recommend to stick with what you know: When you declare Dim Ws As Worksheet (you can't declare it As Sheet!) don't assign a Sheet to it but a Worksheet.

Discuss
0

I think you just need to change this:

If inputform.Range("f12").Value = "Ground" Then
    shippingpapers.Range("K4") = "XXXXX" 
End If

To this:

If Sheets("inputform").Range("f12").Value = "Ground" Then
    Sheets("shippingpapers").Range("K4") = "XXXXX" 
End If

The answer from Variatus is very good, but if you do not feel comfortable enough with such advanced code, then just try my little update and see how it works.

Discuss
0

Mike

Apart from the efficient code from @Variatus and suggestion from @Don (similar to mine earlier), is this the same issue that I responded to yesterday on your question from 30 November? I.e. was the If issue related to additional spaces in cell F12 and resolved by the addition of trimming, as below?

If TRIM(Sheets("input form").Range("F12").Value) = "Ground" Then Sheets("shipping papers").Range("K4") = "XXXXX"
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login