Posts by crow

Hi gijsmo

You're a star!

It works like a charm!

Thanks.

Crow

Hi Folks

Password is: CROW-RA1ND-RO2BY-AR3LE-RENN

Sorry again!

Eric

Hi folks. Maybe crash is an overstatement.

Sorry!

Hi folks

I have a workbook in which I can add initials or delete them ( in the 'Teachers' column - column C). It actually works fine.

The initials are numbered, in the next column (D). No header.

I can delete initials in column C. The numbers will adjust accordingly in Colum D.

The limit on the initials entered is 20 (in column C).

My problem:

I can delete in Column C, ANYWHERE, just by clicking on it, any initial.

HOWEVER, If I delete the FIRST initial in C3 (number 1 in column D), vba acts up!

Please help me to fix it.

Thanks

Eric

Hi Roy

Again, thank you.

Also the comments in the code helps a lot!

I really appreciate it.

crow

Hi Roy

As a follow up.

is it possible to shift up the names once a name (i.e. QQ) had been deleted from

the dynamic ray called Educators?

crow

Hi Roy

Thank you so much. There was a slight mistake in the coding.

The range Range("F9:OP38") was supposed to be ("E9:AP38") .

Other than that, it works like a charm!

I am indebted to you!

crow

Hi Roy

Thanks so much for your help.

I really appreciate it.

From your post/solution I understand that the coding

takes care the problem of me deleting a teacher (i.e. QQ ) somewhere in the middle

in the range "Educators"?

Or do I have to take care of that?

Please advise and thanks again.

crow

Hi dangelor

Yes, there is a named dynamic, named range called 'Educators'.

crow

Hi dangelor

Thanks for assisting me.

The code throws me a curve ball. It says

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("Educators")) Is Nothing Then

Run-time error '1004'

Method 'Range 'of object''_Worksheet failed'

Please help

Thanks

crow

Hi Folks

I wish to CLEAR the (specific) contents on one sheet 1 (huge range) if that specific info had been DELETED on sheet 2 (a dynamic list).

My experience in coding is not not that good at all.

I code by looking for similar examples on Google.

Let me explain. I have a workbook with two sheets, called Allocation and Teachers. On Teachers is a DYNAMIC LIST called Educators.

Whenever a DELETE a name (given by NM, JH , etc.) from the dynamic list (on the sheet called Teachers), ALL CELLS that contain instances of the DELETED name,

on Allocation, must be CLEARED of its contents.

The range on Allocation (from which the name (i.e. NM, JH, etc. must be cleared) is F9:OP38.

I have slapped together 2 Subs in Module 1, but neither of them works.

Please help

Thanks again for your patience.

Eric

Forum2.xlsm

Hi Carim

Thanks a lot! Much obliged.

It works like a charm

crow

Hi Carim

Previously I did not know how to upload an image.
But I have learnt!

Here's the link https://www.dropbox.com/s/xx68…9q/Invigilation.xlsm?dl=0

Thanks in advance.
crow

Hi. graha_karya, are we talking about the same post?

Hi folks. I am setting up an invigilation roster in which an educator can only be used once per day per class. If you attempt to assign the educator again for the same day
then Excel warns you and delete your second assignment of the educator. I can do this for one day, and it works for one day. I struggle have this done for, say, 10 days.

The abbreviations on the right are the codes referring to teachers. The numbers refer to the teacher being placed per day (should be once).
My code for the topmost row (teacher ZAM) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.MoveAfterReturnDirection = xlToRight








Dim iVal As Integer 'Correcct
Dim myRange As Range 'Correct




Set myRange = Application.ActiveWorkbook.Sheets("CountPeriods").Range("C5:R5")




iVal = Application.WorksheetFunction.CountIf(myRange, "AC") Or Application.WorksheetFunction.CountIf(myRange, "ZAM") Or Application.WorksheetFunction.CountIf(myRange, "ZS") _
Or Application.WorksheetFunction.CountIf(myRange, "SF") Or Application.WorksheetFunction.CountIf(myRange, "LJ") Or Application.WorksheetFunction.CountIf(myRange, "NL") _
Or Application.WorksheetFunction.CountIf(myRange, "LM") Or Application.WorksheetFunction.CountIf(myRange, "AMJ") Or Application.WorksheetFunction.CountIf(myRange, "AMV") _
Or Application.WorksheetFunction.CountIf(myRange, "VN") Or Application.WorksheetFunction.CountIf(myRange, "MN") Or Application.WorksheetFunction.CountIf(myRange, "NQ") _
Or Application.WorksheetFunction.CountIf(myRange, "SR") Or Application.WorksheetFunction.CountIf(myRange, "GS") Or Application.WorksheetFunction.CountIf(myRange, "DS") _
Or Application.WorksheetFunction.CountIf(myRange, "CS") Or Application.WorksheetFunction.CountIf(myRange, "PS") Or Application.WorksheetFunction.CountIf(myRange, "JT") _
Or Application.WorksheetFunction.CountIf(myRange, "MY")




If iVal > 1 Then
On Error Resume Next
MsgBox "This teacher had ALREADY been entered once!"
Selection.Offset(0, -1).ClearContents
Selection.Offset(0, -1).Select
Exit Sub
End If


End Sub

Please help.

Thanks in advance.
Crow

Note the attachment

Re: Next empty column and next empty row

22 April 2017

Hi Pike

It worked likes a charm.

Thanks!

Ps. I am late in acknowledging. Sorry!

Hallo folks
I have started an application in Excel.
Column 1 or A contains codes of people names.
I want the vba code to find the next empty column (which will be B).
I want to place data in B until i have reached the length
of FILLED column A (Find the next empty row in column B)

Only then I want the code to find the next empty column.
The code to find the next empty column is as follows and works just fine:

Dim NextEmptyCol As Long
NextEmptyCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1

MsgBox "Column Number" & " " & NextEmptyCol & vbCr & _
"Or column letter """ & Replace(Cells(1, NextEmptyCol).Address(0, 0), 1, "") & """", vbInformation, "The next empty Column is ..."

[TABLE="width: 500, align: center"]

A

B

C

D

E

Teachers

AC

PT

JC

WT

[/TABLE]
I need assistance filling column B with data until the length of filled
column A had been reached. I can use all assistance I can get.

Thanks folks.

crow

Re: Identify cell Learner no above active cel

And it worked like a charm!
Much obliged!
Eric

Hallo good people. Greetings to you.[TABLE="class: grid, width: 500, align: center"]

A

B

C

D

E

F

G

H

1

Learner No

1

2

3

4

5

6

7

2

March

3

3

2

1

3

2

2

3

June

1

1

3

2

1

3

1

2

Sept

2

1

3

2

1

1

2

5

December

2

1

3

2

1

1

2

[/TABLE]
In the Excel table above the numbers in red identifies the code a learner obtained for a subject, lets say History. The numbers in blue represent
a learner a specific learner). When the user selects any of the cells in RED, the corresponding number in blue, at the top of that
column have to chosen and used in a vlookup or hlookup table to identify the learner from a second table hidden somewhere else.

My problem writing the code that select/copy the number in blue once the user click any of the red numbers. Can someone help please.
I thank.
Eric

I need assistance in:
(a). Keeping a combobox placed on an Excel worksheet in view as you scroll down on the Excel sheet AND
(b). having made a selection from the combobox, the cursor needs to go to a specific range (cell).

I thank you.

crow