In cell C12 if0 Copy C12 and A12 to A63 Solved/Closed

Question

Hello, 
First thank you for the quick response!!! Thank you!

You were correct with the formula. I have that in A63 =if(C12>0,C12&" "&A12," ")
If you use this in A64, A65, A66.... there are blanks if you don't have >0 values in all the spaces.

The trick is to get the spaces gone if zeros exist.

The question

In cell C12 if>0 Copy C12 and A12 to A63
If C12 is not >0 do nothing
Now, in C13, and >0 look at A63. Since C12 did nothing, C13 populates A63.
Then C14 has value >0, it see A63 populated, so it moves to A64 and populates.
Then C15 is 0, so it does nothing.
C16 is >0 so now it has to look for next line below A64. C16 would then populate on A65. and so on...
Does this paint a better picture?
Again, impressed with response time,
Thank you again
Steve



Configuration: Windows Vista / Internet Explorer 7.0

TrowaD · Answer

Hi Steve,

Now that I understand your query I can say that it is a very tricky one.

Basically I know two methods, but since the first one has limitations and the second one takes a lot of work I doubt you will find them usefull, but let's give it a try.

1. Use nested If's:
=IF(C12>0,C12&" "&A12,IF(C13>0,C13&" "&A13,IF(C14>0,C14&" "&A14, ... etc.
You are limited to nest 7 IF's.

2. Use VB code:
Sub test()
Range("A63").Formula = "=IF(C12>0,C12&"" ""&A12,"""")"
    If Range("A63").Value = "" Then
Range("A63").Formula = "=IF(C13>0,C13&"" ""&A13,"""")"
        End If
    If Range("A63").Value = "" Then
Range("A63").Formula = "=IF(C14>0,C14&"" ""&A14,"""")"
        End If
'Repeat for as many cells of data you have.
    If Range("A63").Formula = "=IF(C12>0,C12&"" ""&A12,"""")" Then
Range("A64").Formula = "=IF(C13>0,C13&"" ""&A13,"""")"
        End If
    If Range("A63").Formula = "=IF(C13>0,C13&"" ""&A13,"""")" Then
Range("A64").Formula = "=IF(C14>0,C14&"" ""&A14,"""")"
        End If
'... etc.
End Sub
Depending on how much data you have this will take a lot of time.

Please note that I have still much to learn and that someone else could write a more effective code for you.

This is all I can offer you, sorry it's not a solid solution.

Best regards,
Trowa

startbuck · Answer

trowa,
Thank you very much for your effort.
This will help or I may just have to settle doing it manually.
I will work with this for several days and see how I do.
Again thank you for you time and effort.
Sincerely,
Steve

In cell C12 if>0 Copy C12 and A12 to A63

2 responses

Similar discussions

Subscribe To Our Newsletter!