VBA: If Row has Content, Populate Column

[SteveOranjin]

Hi,

I have a formula that is working at a subsistence level that I want to get operating in a better way. What the Formula I have does

Take Data from Cells in Sheet2 and Populate the first 1,000 rows in sheet 1 with it.


What I'd like this formula to do instead is

• Create two new columns, insert them BEFORE the existing A&B Columns. So A and B are now C and D.
  • ​With the new Row Enter Into Cell A1: "Brandname"
  • With the new Row Enter Into Cell B1: "Brandcode"
• Grab the content from Cells Q3 and R3 in "Sheet2"
• Populate all of A and B cells with the values from Q3 and R3 where there is content in the row.
  • Clarification: So if 1000C has any value in it at all, put the values from Q3 and R3 into A1000 and B1000
  • If D1001 has data in it, also populate A1001 and B1001 with the designated values

Below, is the formula I have working now. I don't imagine it will be much help, but your welcome to take a look.

Sub First_2_Column_Set_Up()'
' First_2_Column_Set_Up Macro
'


'
    ActiveWindow.ScrollColumn = 2
    Range("Q3:R3").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:B1073")
    Range("A2:B1047").Select
    Sheets("Sheet2").Select
    Range("B1").Select
End Sub

 

[SteveOranjin]

Can I get a little love here?

 

[Fluff]

Is this what you're after?

Sub AddData()

   With Sheets("Sheet1")
      .Columns(1).Resize(, 2).Insert
      .Range("A1:B1").Value = Array("Brandname", "BrandCode")
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
         .Offset(, -2).Value = Sheets("Sheet2").Range("Q3").Value
         .Offset(, -1).Value = Sheets("Sheet2").Range("R3").Value
      End With
   End With
      
End Sub

 

[SteveOranjin]

How do I get as good as you are so that I can just whip out a little VBA off the cuff like this.

 

[Fluff]

I've primarily learnt from helping people here & looking at how other people do things.
But one of the members here put together a list of good sites which you can find here
https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions.html

 

[SteveOranjin]

Hello,

I am using the following code, but it seems to be having a number of problems. So when I initially enter data into this sheet, I always enter it as, "Paste Values". The problem is, when I then activate the formula, it A.) duplicates all of the data that is already in the sheet, B.) it re-formats all of the data so it comes in with all of the pre-configured formatting that I removed. But it only does it to one of the two sets of data, not both.

I'm not deeply familiar with VBA, but I'm pretty sure this shouldn't be happening. I'm thinking it's either something to do with the code, or it has something to do with a different VBA operation that I have entered in.

The formula is designed to enter 2 columns of data in row "A", and "B" and populate them with information I have entered in a different field.

Code:
Sub AddData()

With Sheets("Sheet1")
.Columns(1).Resize(, 2).Insert
.Range("A1:B1").Value = Array("Brandname", "BrandCode")
With .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
.Offset(, -2).Value = Sheets("Sheet2").Range("Q3").Value
.Offset(, -1).Value = Sheets("Sheet2").Range("R3").Value
End With
End With

End Sub
Steve

 

[Fluff]

Firstly (a piece of friendly advice) that is a macro (or code) not a formula. By referring to a macro as a formula you are
a) Likely to confuse people
b) Limit the number of potential helpers. eg if you had put Formula in the thread title, I (and others) wouldn't have looked at the thread, as I predominantly help with VBA.
As to your problems
That code shouldn't be changing any formats as it's just copying values. If formats are changing it's either due to other macros, or conditional formatting.
Also for any row where col C (in sheet1) has a value it will insert the values from Q3 & R3 on sheet2 into cols A & B for that row. Is that not happening, or is that not what you want?

 

[SteveOranjin]

Fluff,

Thanks for your friendly advice. I'm not always as careful when I'm typing as I should be.

The code there is performing all it is supposed to do. It is just also performing these other actions as well. I'm not sure I made myself clear, so I'll try re-wording what I said.

It is doing two things it shouldn't be doing (At times. Under what circumstances it performs these actions, and what times it doesn't I have not determined a common denominator)
- It is
- Pasting the entire set of data at the END of the original set of data. So they are flush. So, if the original data set had 5 columns, it would now have 10. The only columns it is not duplicating are the ones that
are inserted.
- It is somehow adding the original formats to one of, not both of, the data sets.

Hope I'm making myself clear here. I really appreciate you taking the time to help me.

Steve

 

[Fluff]

That sounds like you have an event of somekind that is being triggered, try

Sub AddData()
Application.EnableEvents = False
   With Sheets("Sheet1")
      .Columns(1).Resize(, 2).Insert
      .Range("A1:B1").Value = Array("Brandname", "BrandCode")
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
         .Offset(, -2).Value = Sheets("Sheet2").Range("Q3").Value
         .Offset(, -1).Value = Sheets("Sheet2").Range("R3").Value
      End With
   End With
Application.EnableEvents = True
End Sub