I have a trial balance on sheet1 and a profit and loss on sheet2. I
can move the numbers on to sheet 2 using a sub(), but I have to use a
designated cell viz s1!a2=s2!a2, but where s1a2 is empty, I dont want
a profit and loss with a whole lot of zeros. One solution would be to
post all and then make a macro to delete ety numvals, but I would
rather post it straight using a loop with a next or skip to post the
next account with a numval to the next line. Any idea of how I could
go about it please

---

Clarification of Question by leetim-ga on 25 Aug 2004 11:53 PDT

Dreamboat, you are trying hard for me - and I appreciate it. Your loop
worked, no syntax errors, but it only transfers the WHOLE of the data
to sheet 2. Would you mind trying it yourself. In sheet 1 for col A1
write accy a2 bchgs a3 clean
a4 depn a5 elect and in b1 10 b2 20 b3 30 b4 40 b5 50. 
Then run your loop. See it transfers all - well and good. Now put 0 in b2 and 0
in b4. Run it again. You will see it transfers it same. 
What I want is for bchgs and depn NOT to be transferred and accy to
appear in sheet 2 a1 clean a2(NOT a3) and elect a3.
In other words to transfer it in a list only showing the accounts
WHERE VALUES are positive.

---

Request for Question Clarification by maniac-ga on 25 Aug 2004 17:41 PDT

Hello Leetim,

Did you get an acceptable answer from Dreamboat?

If so, I suggest you close the question so you don't get charged by a
researcher for another answer.

If not, I can solve this a couple different ways depending on the
limits you want on the profit & loss sheet.

For example - you state that zeros are not acceptable. If blank lines
are acceptable, a macro is not required but formulas using =if will do
the job. Alternatively, you could suppress the zero (or blank lines)
by using a filter on the profit & loss sheet. However, if you just
want non zero values (with say 90 lines on the P&L sheet instead of
100 on the trial balance sheet), I can prepare a short macro (not much
longer than Dreamboat's) that will do the job more properly.

If one of these solutions is OK, please indicate in a question
clarification and also indicate if you want to skip column headers
(e.g., row 1) as well.

  --Maniac

Try this:

If Sheets(SheetName).Range(YourRange) = vbNullString Then 
     'Skip This one
Else 
     'Code Here to do what you want
End If

I have not tried that yet Dreamboat as it is late and just come in,
but looking at it - it skips the null okay (lets say b2) but then for
c2 with a value, where do I put this to? I mean it must go on s2b2
now, because there is nothing posted to s2b2, but how can I tell it to
go there when I dont know which cells are going to have null values or
not. See the trial balance changes from one client to another. What I
am looking for is the code to post the s1c2 to 'the next free line on
s2' if you see what I mean? If you dont think that is possible, I will
have to make a work around with a template profit and loss account to
have posted to in null values, and then a macro to delete all lines
with a 0. But the post to next line would be preferable. Thanks a lot.

Working on it for you. :)

If Sheets("Sheet1").Range("A1") = vbNullString Then 
     'Skip This one
Else 
    Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value 
End If 


Or with a loop


Option Explicit 
 
Sub Test() 
     
    Dim Cell            As Range 
     
    For Each Cell In Sheets("Sheet1").UsedRange 
        If Sheets("Sheet1").Range(Cell.Address).Value = vbNullString Then 
             'Skip
        Else 
            Sheets("Sheet2").Range(Cell.Address).Value = _ 
            Sheets("Sheet1").Range(Cell.Address).Value 
        End If 
    Next 
     
End Sub

It sounds to me like you could use some VBA Training and Certification. :)

Sure could use some training Dreamboat - been on 2 intermediate excel
courses at Canning College 2hrs by 8 weeks each, and have the basics.
Unfortunately they dont offer programming per se - more in line with
macros and worksheet functions. But have the rudiments. These
questions I am asking are TOUGH. I am a ball of fire with dbase III+
programming and have a gen ledger prog we use ALL the time in the
practice. Running on win98se am still in the ballgame, but I can see
the day when I will HAVE to upgrade to XP for internet. A lot of our
work is with thin clients now, and you need java jive to get there! My
clerk was good too. You ought to see the conversion prog we have for
the gen ledger. Drop down menus, button macros and all the like. We
got everything done, bar the wretched transfer to profit and loss from
cashbook. He ran out of steam and has left to go back to his country
of origin. All I have to do is conquer this, and with your help, one
way or another I will come up with it. With prog real life situations
are the only way to go. Employed comp science grads straight out of
univ, but the dont know. Passed the exams, but ask the to write a real
life prog - no hope. I dont have to tell you this - you know it better
than me.
Will put your fine advice to work tonight and thanks SO much for your help

You can find Excel VBA Training and Certification will be available
any day now (gee, how do I know that?? hee hee) at
www.vbaexpress.com/forum.