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
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.)
If statement part wont run what did I do wrong?
0
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.
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.
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: 4889) 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: 1989) Dec 7, '20 at 12:01 am
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.
0
I think you just need to change this:
If inputform.Range("f12").Value = "Ground" Then
shippingpapers.Range("K4") = "XXXXX"
End IfTo this:
If Sheets("inputform").Range("f12").Value = "Ground" Then
Sheets("shippingpapers").Range("K4") = "XXXXX"
End IfThe 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.
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"