A loop question & write texts

[snoopies]

Dear all,

Pls kindly help ... Many thanks!

Col A - Code no
Col B - Headers & Information
Col C onwards - Job no
e.g Col C - Job no 1
Col D - Job no 2

With attached file, it shows that each job no. has different information
(i.e will not contain info of ALL Headers), some are missings and therefore
leave blank cells there. My problem is... how to write the remarks " No records" for each job show there ? (the job no. is not fixed, there may be job no 3 in Col E, job no 4 in Col F if required)

Regards.

[Jacob Hilderbrand]

Ok, I do not follow the logic. In the attachment you have No Records in different columns. What is the criteria to write this?

For example: In row 2 you have No Records in Col D, but Col C is also blank.

Please clarify.

To get you started you can do something like this.

Dim LastRow As Long
Dim i As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 2 to LastRow
If <Criteria Here> Then
Range("C" & i).Value = "No Records"
End If
Next i

[acw]

Hi

Try this

Sub aaa()
 nocols = Range("IV1").End(xlToLeft).Column - 2
For Each ce In Range("a2:a" & Range("a65536").End(xlUp).Row)
  If Not IsEmpty(ce) Then
   For i = 2 To nocols + 1
    If IsEmpty(ce.Offset(1, i)) Then ce.Offset(0, i) = "No Record !"
   Next i
  End If
 Next ce
End Sub

There is an anomoly with regard to RefCode D. Not sure what should be here.

Tony

[snoopies]

Deal All,

Thx for your reply, sorry I made a mistake regarding to col D...
I should put a "No Record" for Job no.001 as well.

The logic is.. if all cells are blank under a header, I should put "No record" for that header (with the column of its job no).

Hope it's clear now.

Regards.

[snoopies]

Hi acw, I try to run the codes, but it doesn't work...

[acw]

Hi

Code runs for me and put results in D2, C7, C11, C14, D14, D16 and D20 of the sample file you gave.

What are you getting?


Tony

[snoopies]

Hi Tony,

Sorry to bother you again...

In real situation,data in col A should be in col H, while Job no. starts from col H onwards.
I try to modify it by myself, but the result seems not satisfactory..:<
Please kindly note that any cell non-blank between two headers.. can't mark "no record", thanks

[acw]

HI

Can you provide a sample file that reflects what you actually have, and show how the result should be formatted.


Tony

[snoopies]

sure, pls find the attached, thx!

[acw]

Hi

try this.

Sub aaa()
    nocols = Range("IV1").End(xlToLeft).Column
For Each ce In Range("a2:a" & Range("a65536").End(xlUp).Row)
        If Not IsEmpty(ce.Offset(0, 6)) Then
            For i = 8 To nocols
                If IsEmpty(ce.Offset(1, i - 1)) Then ce.Offset(0, i - 1) = "No Record"
            Next i
        End If
    Next ce
End Sub

There is a difference in the output due to the "super headings". From the sample, the super heading should only have No Record if all the sub heading items have No Record (as in J003). However, the only thing that seemed to define a super heading was the Yellow background, or a leading 3 chars of TV: Are these valid ways of defining a super heading???


Tony

[snoopies]

Hi,

You may think each yellow cell refers to a super header.. I need to do the groupings myself..

I'm not familiar with the use of offset, could you kindly explain a bit on your codes? coz' I want to learn more from your codes..

Really thanks!

[acw]

Hi

Offset is used to determine the value of a cell that is a certain number of rows and/or columns away from the present cell.

Offset(a1,1,0) will be A2 (down 1 row)
Offset(a1,0,1) will be B1 (across 1 column)
Offset (a1,1,1) will be B2 (1down, 1 across)

You can use negative numbers in the offsets.

offset(b2,-1,-1) will be A1 (1up, 1left).

The advantage is that you can use a current cell position, and go a fixed distance from it pretty easily. So by looping through the items in column A, you can tell if there is an item in column G (ce.offset(0,6)) and if there is then test for an entry in the first row of the data.

The macro as it stands will require that the first row of the data contains something. If not, it will put in the "no record" text.

Tony

[snoopies]

Hi,

Thanks for your teaching!

I shall be grateful if you can help me to clarify following points..

1> Does the variable "ce" refers to Cell? and I don't need to define this variable if I use option explicit at the top...?

2>It's a bit difficult for me to understand the whole meaning of this sentence..

   If IsEmpty(ce.Offset(1, i - 1)) Then ce.Offset(0, i - 1) = "No Record"

can you give me an example, e.g "IsEmpty(ce.Offset(1, i - 1))" & "ce.Offset(0, i - 1) " --> which cells will be selected ?

Thanks again!

[acw]

Hi

1) ce is a variable that houses each cell in the range as it cycles through them all. If option explicit is selected, then the variable will have to be defined. So if your range is A2:A8, then it will cycle through A2, A3, A4... A8.

2) What it is doing is looking at a cell that is 1 row down, and a certain number of columns to the right. Basically it is looking for the first row of data in each of the columns and if it is empty, then put the heading on the same row, for the data column. Say your data is in columns H:J (ie columns 8 - 10). So if you are in A2, and H3 is blank (isempty(ce.offset(1, i-1)) then it will put the output into H2.


Tony