-1

very new to VBA (this is day 2). Most of my experience so far is a mix of code from over the web and a great help on a previous question on here. So i have a new question. I am importing data from a mass of excel sheets into one consolidated sheet as a record. This bit works fine, it then gets copied onto a seperate sheet in a format that suits the user. However, i would like a formula to come across to, i dont know how to do this at all. I would like it to be applied to be each row and be specific for that row. 

    Sheets("TrackRecord").Range("B2:B9999").Value = Sheets("Data").Range("D2:D9999").Value
    'Copy and paste for Quote Number
    Sheets("TrackRecord").Range("C2:C9999").Value = Sheets("Data").Range("N2:N9999").Value
    'Copy and paste for Cable OD
    Sheets("TrackRecord").Range("E2:E9999").Value = Sheets("Data").Range("P2:P9999").Value
    'Copy and paste for Wall Thickness Proposed
    Sheets("TrackRecord").Range("F2:F9999").Value = Sheets("Data").Range("Q2:Q9999").Value
    'Copy and paste for Wall Thickness Override

In the above i would like an IF statement to be pasted on each row in Column D which basically says if(F2<>"",F2,E2) and then this be sequential for each row. Ultimately i would like not to bring the bottom two lines through and do this before it even gets on the page, if that could be done it would be amazing!

Quick question i need it to start in row 2 but i dont want it to end, do i have to put D9999 or can i do something else for an infinite level? Sorry.

Thank you for reading this far, an thank you in advance for your help :)

Steven Byrne
  • 134
  • 10
  • 1
    Re: quick question, [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – BigBen Mar 31 '20 at 15:10

1 Answers1

1

Try:

Sub testNiceCode()
   Dim shD As Worksheet, shTr As Worksheet, lastRowD As Long, lastRowTr As Long
   Set shD = Sheets("Data")
   Set shTr = Sheets("TrackRecord")
   lastRowTr = shTr.Range("B" & Rows.Count).End(xlUp).Row 'B because you do not have data in D:D...
   lastRowD = shTr.Range("B" & Rows.Count).End(xlUp).Row  'if all columns have the same number of rows
   shTr.Range("B2:B" & lastRowD).value = shD.Range("D2:D" & lastRowD).value
   shTr.Range("C2:C" & lastRowD).value = shD.Range("N2:N" & lastRowD).value
   shTr.Range("E2:E" & lastRowD).value = shD.Range("P2:P" & lastRowD).value
   shTr.Range("F2:F" & lastRowD).value = shD.Range("Q2:Q" & lastRowD).value
   shTr.Range("D2:D" & lastRowTr).Formula = "=IF(F2<>"""",F2,E2)"
End Sub

If your columns in "Data" sheet do not have the same number of rows, you must create a lastRow variable for each of them.

And lastRowTr is calculated using the B:B range, because the D:D range does not contain values...

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Hi @FaneDuru i am using the bottom, one it seems to work, however, it overrides the title in cell D1 and adds the formula in there, how do i get it to start in D2? Thank you by the way, appreciate your help. – Steven Byrne Apr 01 '20 at 14:18
  • I have tried this but it errors. Sheets("TrackRecord").Range("D2:D" & Sheets("TrackRecord").Range("D" & Rows.Count).End(xlUp).Offset(0, 1).Row).Formula = "=IF(F2<>"""",F2,E2)" not too sure where the offset should go if that is the answer – Steven Byrne Apr 01 '20 at 14:23
  • Aaah sussed it, Sheets("TrackRecord").Range("D2:D" & Sheets("TrackRecord").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Row).Formula = "=IF(F2<>"""",F2,E2)" not without your help though! Thank you very much! – Steven Byrne Apr 01 '20 at 14:28
  • @Steven Byrne: Glad I could help. But, it is strange, since the range starts from D2 and your modification only change the last cell of the column, not the first, extending the range with one row... I would suggest you to try using (again) the code as it is. But written with attention... – FaneDuru Apr 01 '20 at 14:43
  • i copied and pasted your formula and changed Data to TrackRecord as this is where i want it to appear. If i dont do the offset it overwrites the title, should the Range "D" be range "D2" before the rows count. The other thing noticed it is simply just adding one each time it is added, for example i did an import which was one row, that worked fine, then i did another one row and that was fine, the next one had 20 rows and it only added 1 row for the formula. Could the count be on say column B? Thanks again for all your help, really appreciate it! :) – Steven Byrne Apr 01 '20 at 15:00
  • @Steven Byrne: When you need to `Debug` such things you have to split the code in small portions: `Dim lastRow as Long, sh As Worksheet`. Then: `Set sh = Sheets("TrackRecord")` and `lastRow =sh.Range("D" & Rows.Count).End(xlUp).Row` and after that: `Debug.Print lastRow: Stop`. The code will stop and you will see in `Immediate Window` (Ctrl + G, in VBE) the value of the last row. Now, the code will become: `sh.Range("D2:D" & lastRow).Formula = "=If(F2<>"""",F2,E2)"`. Does it work as expected? – FaneDuru Apr 01 '20 at 15:20
  • thank you for that, i am going to have a play with that now and see how it works. Really appreciate you helping. I have made one small change to the code and it now works as expected without the offset like you suggested was not needed. Sheets("TrackRecord").Range("D2:D" & Sheets("TrackRecord").Range("B" & Rows.Count).End(xlUp).Row).Formula = "=IF(F2<>"""",F2,E2)" – Steven Byrne Apr 01 '20 at 15:25
  • This is how it appears now: Sheets("TrackRecord").Range("B2:B9999").Value = Sheets("Data").Range("D2:D9999").Value Sheets("TrackRecord").Range("C2:C9999").Value = Sheets("Data").Range("N2:N9999").Value Sheets("TrackRecord").Range("E2:E9999").Value = Sheets("Data").Range("P2:P9999").Value Sheets("TrackRecord").Range("F2:F9999").Value = Sheets("Data").Range("Q2:Q9999").Value Sheets("TrackRecord").Range("D2:D" & Sheets("TrackRecord").Range("B" & Rows.Count).End(xlUp).Row).Formula = "=IF(F2<>"""",F2,E2)" – Steven Byrne Apr 01 '20 at 15:28
  • @Steven Byrne: But this code is still primitive, no offence... I have shown you how to declare the sheet and you do not use that... I will adapt my code with a full code. Do your columns in the two sheets have the same number of rows foe each sheet? So I will prepare my answer. If not, you must use a `lastRow` variable for each row (`lastRowC`, `lastRorwE` etc.). – FaneDuru Apr 01 '20 at 16:08
  • no offense taken, as i said i am very new to this and you have far more experience so I am taking it on bored. Yes both sheets will have the same amount of rows. I didnt understand the debug part or how to get that to work. I have noticed you have amended the answer so i will try that and see how i get on. Thank you again for your help. – Steven Byrne Apr 02 '20 at 05:26
  • So i copied and pasted the new formula in exactly as is, made no changes. I currently have 20 rows of data on my 'Data' Sheet (this excludes the header). I ran the code, it imported 1 row of data, and on the column that has the formula, it overwrote the header. Any suggestions? Also the formula that is overwrote the header is referring to row 2 and the one on row 2 is referring to row 3. I dont understand why the formula isnt going into the right place when the rest is? I promise you, i have copied and pasted your code. – Steven Byrne Apr 02 '20 at 08:50
  • @Steven Byrne: Let us take them step by step: 1. In order to use a good debug tool, you must be in VBE (Visual Basic Editor). Press `Alt + F11`. Then Press `Ctrl + G`. Then, `Immediate Window` will show up (if not already visible). You will be able to see the return of `Debug.Print`. 2. Now, use my previous suggestion: `Debug.Print lastRowTr: Stop`, and run the code. Insert this line after `lastRowD =...`. It will stop on `Stop` command. What value of lastRowTr can you see in `Immediate Window`? – FaneDuru Apr 02 '20 at 09:03
  • i have done that, and the Immediate Window now has a lot of 1's. 21 in total. I have added it after shTr.Range("D2:D" & lastRowTr).Formula = "=IF(F2<>"""",F2,E2)" and before End Sub is this correct? – Steven Byrne Apr 02 '20 at 09:13
  • Hello @FaneDuru, i just watched a video on how the immediate window works and that showed me the "F8" button. Using that i have changed the LastRowTr = shD and LastRowD = shD and it all seems to work now, the formula works and appears in the correct cell to. I dont understand why though. And the debug says 21. – Steven Byrne Apr 02 '20 at 09:27
  • `Insert this line after lastRowD =...` I said... You must receive the number of rows (only once...) in column B:B. Isn't it true? – FaneDuru Apr 02 '20 at 11:28