Pasting into an existing column

[jdautel]

Ok, I'm new to vba as of right now but learning to help people more efficient in my office. Currently we use quickbooks and when exporting financials to excel it returns something like this:

Income
Maintenance
		Landscaping
	Total Commerical
	Landscaping
		Landscaping
			Customer Discount
			Landscaping - Other Residential
		Total Landscaping

So I've created a vba code that literally hardcodes cut and pastes every single one of these cells and puts them into column a
For instance:

Sheets("New").Select
Range("B5:B21").Cut Range("A5:A21")

I done this for every single cell. This works for this specific worksheet but if I export another worksheet that has a different set up it won't accomplish the task correctly.

I'm trying to create a code where I can cut a cell based on certain criteria, and paste it into that same row number but in the A column. Offsetting won't seem to work because some of the text is in column b, some in c, and some in D. I was thinking I could create an if statement that has the following logic: If cell is in column B offset by (0,-1), if in column C offset by (0,-2), etc, etc.

[Kenneth Hobs]

Welcome to the forum!

Merged cells are the bane of programmers. In any case, something like?

Sub Main()  Dim r As Range
  With Worksheets("New")
    Set r = .Range("B5:B21")
    r.Cut .Range(.Range("A" & r.Row), .Range("A" & r.Rows.Count + r.Row - 1))
  End With
End Sub

[jdautel]

Awesome, thanks Kenneth. This means I could run an if statement through for different text strings (include all possible text strings that could show up) and then place them in the correct column using r.Row?

[jdautel]

I'm just trying to figure out the absolute best way to automate this process for people using VBA.

[p45cal]

As Kenenth says, merged cells are a mjor pain.
Seriously consider exporting from Quickbooks in a different format and then opening the resultant file in Excel. See https://www.sleeter.com/blog/2014/02...reports-excel/
We can help you with your further analysis of such files.

I see also there's an option when exporting to Excel to create a csv file: http://www.accountingweb.com/technol...ports-to-excel
This could be the best way.

[Kenneth Hobs]

As p45cal said, exporting your data into csv might be the best approach as both programs can deal with that pseudo database file type.

While you can use If()'s, Select Case may be the better choice. It just depends what your goals are using the data. A Range.Find() can find what columns key words are at.

Of course providing an obfuscated simple csv and expected outcome file is a good way to get specific help. Click the Go Advanced button in lower right of a reply and then the paperclip icon in toolbar to Browse and Upload file(s).

[jdautel]

Ok, I don't have access to a freshly exported file right now, need to wait until I get that and I can attach it to show. Thanks for helping so quick though and I'll post it as soon as I have it.

That CSV page might be the way to do it, especially if you can autorefresh the P&L data

[jdautel]

I'm going to just export it as a CSV from now on and create a macro to format it in the way I like. Thanks for the help guys