How do I combine MACROS and functions?


M

Mr_Crowe

I'm copying and pasting onto excel from an accounting program, and it's
pretty inconsistant. Whenever a new heading pops up, it either deposits the
first cell in the A column, and the second cell in the C column, or it
deposits the first cell in the B column, and the second cell in the C column.
I can write a function to identify this
=IF(A#="",IF(B#="","","deleteA"),"deleteB") where # stands for whatever row I
happen to be in. And I figured out how to write a MACRO to delete a specific
cell. But with six thousand rows, and doing this pretty often, it sure would
be nice to automate it. Any ideas?
 
Ad

Advertisements

R

Ron Rosenfeld

I'm copying and pasting onto excel from an accounting program, and it's
pretty inconsistant. Whenever a new heading pops up, it either deposits the
first cell in the A column, and the second cell in the C column, or it
deposits the first cell in the B column, and the second cell in the C column.
I can write a function to identify this
=IF(A#="",IF(B#="","","deleteA"),"deleteB") where # stands for whatever row I
happen to be in. And I figured out how to write a MACRO to delete a specific
cell. But with six thousand rows, and doing this pretty often, it sure would
be nice to automate it. Any ideas?

I'm not sure what you want to do. The logic of your function says
if there is anything in A#, delete whatever is in B#,
otherwise if there is anything in B#, delete whatever is in A#.
And don't delete anything if there is nothing in A# or B#.

But the second part is redundant since there couldn't be anything in A# to
delete.

Your description suggests that there will be data in either A# or B#.

Perhaps something like this would put all the new headings into col A:

=============
Option Explicit
Sub Foo()
Dim c As Range
For Each c In Range("A1:A6000")
With c
If .Value = "" Then .Value = .Offset(0, 1).Value
.Offset(0, 1).ClearContents
End With
Next c
End Sub
================

But again, I'm not sure if that is what you want.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top