VB Code for equal assignment

M

MaheshBabu

New Member
Joined
Nov 5, 2013
Messages
7
Hi Every1,

I have 3 columns in an excel sheet (ID, Signed by & To be checked), and in another sheet, I have names under a column "Checkers".

I need a macro where in if I run it "To be checked" column in sheet 1 should be equally assigned to the "Checkers" in sheet 2 and the rest should be blanks.

Some1 help me with the macro.

Please find the below sample:

Before Macro:

[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Signed by[/TD]
[TD]To be Checked[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]AAA[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BBB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]CCC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]BBB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]CCC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]EEE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

After Macro :

[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Loan #[/TD]
[TD]Signed by[/TD]
[TD]To be Checked[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]AAA[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BBB[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]CCC[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]AAA[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]BBB[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]CCC[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]DDD[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD]AAA[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]EEE[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
MaheshBabu,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


and in another sheet, I have names under a column "Checkers".
Click to expand...

Can you post the information, and, the worksheet name, for the above mentioned quote?
 
Upvote 0
Hi,

am unable to attach the excel file..

In 'sheet1'

[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Signed by[/TD]
[TD]To be Checked
[/TD]
[/TR]
[TR]
[TD]121
[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BBB
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]CCC[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]AAA[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]BBB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]CCC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]EEE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]DDD[/TD]
[/TR]
</tbody>[/TABLE]


In 'sheet2'

[TABLE="width: 72"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Checkers
[/TD]
[/TR]
[TR]
[TD]Don
[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Rose[/TD]
[/TR]
</tbody>[/TABLE]


After running a macro i want output in the below format:


[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Signed by[/TD]
[TD]To be Checked[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]AAA[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BBB[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]CCC[/TD]
[TD]Don[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]AAA[/TD]
[TD]Adam
[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]BBB[/TD]
[TD]Adam
[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]CCC[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]DDD[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD]AAA[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]EEE[/TD]
[TD]Rose[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

If, i have 10 rows in 'sheet1' and 3 'checkers' in 'sheet2'

each checker should get 3 ID's and the rest should be blank as shown in the above example.

Let me know if any information required.

Thanks in advance.
 
Upvote 0
MaheshBabu,

What is the logic for assigning Checkers in Sheet1 for To be Checked in column C?

In Sheet2 we just have Checkers.

Sheet2 should probably contain another column(s) ID or Signed by?????
 
Upvote 0
Hi,

Sorry for the confusion....lemme xplain you once again:

For example 'Sheet1' have the below columns

[TABLE="width: 241"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Signed by[/TD]
[TD]To be Checked[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BBB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]CCC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]BBB[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]CCC[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD]DDD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD]AAA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD]EEE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]DDD
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Some where in the 'sheet1' need not be only 'sheet2' i have dedicated checkers to review the ID's

Say i have 3 checkers viz.,
Don
Adam
Rose

I need to assign ID's provided in 'sheet1' to the above 3 checkers equally under 'To be Checked' column of 'sheet1'.

Output should come in the 'sheet1' itself.

The moment i run a macro 'To be checked' column should be filled equally and the rest of the rows should be blanks.

Hope it is clear now.

Thanks in advance for the support.
 
Upvote 0
MaheshBabu,

Sample worksheets:


Excel 2007
A
1Checkers
2Don
3Adam
4Rose
5
Sheet2



Excel 2007
ABC
1IDSigned byTo be Checked
2121AAA
3123BBB
4124CCC
5125AAA
6126BBB
7127CCC
8128DDD
9129AAA
10130EEE
11131DDD
12
Sheet1


After the macro in worksheet Sheet1:


Excel 2007
ABC
1IDSigned byTo be Checked
2121AAADon
3123BBBDon
4124CCCDon
5125AAAAdam
6126BBBAdam
7127CCCAdam
8128DDDRose
9129AAARose
10130EEERose
11131DDD
12
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub AssignCheckers()
' hiker95, 11/06/2013
' http://www.mrexcel.com/forum/excel-questions/737261-vbulletin-code-equal-assignment.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long, n As Long
With Sheets("Sheet2")
  c = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
With Sheets("Sheet1")
  a = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row)
  n = Application.Floor(UBound(a, 1), UBound(c, 1))
  ii = 0
  For i = 1 To n Step 3
    ii = ii + 1
    a(i, 3) = c(ii, 1)
    a(i + 1, 3) = c(ii, 1)
    a(i + 2, 3) = c(ii, 1)
    If ii = 3 Then ii = 0
  Next i
  .Cells(2, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the AssignCheckers macro.
 
Upvote 0
hiker95,

Thank you so much for the code.....its working fine but....

in the give example i have given you 10 ID's and 3 checkers....

While i work i will get 100's of ID's and checkers also not restricted to 3 it may increase.....

i want a code in such a way that if my ID's and No. of Checkers are not constant(It may increase/decrease) the ID's in sheet 1 should be equally assigned to the checkers in a given range of cells be it 'sheet2' are any where in the excel....and the rest of the rows should be blank....

Can you gimme the modified code which meets the above criteria......

Thanks in advance...
 
Upvote 0
MaheshBabu,

I have been trying to figure out the correct logic to your latest request.

But, it is very late, and, I will be trying again tomorrow.


In the mean time, if you can not wait until tomorrow, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
MaheshBabu,

I still have not been able to resolve your latest request.


Prince27,

If you have a solution for MaheshBabu, then please post it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Hi. I am looking for an excel formular to filter the data with columns heading a-g if columns heading dog to house has "new" in the cell.
2
Replies
10
Views
528
Fluff
Fluff
F
  • Question Question
Allocating cost based on balance on PO line
2
Replies
12
Views
610
Tetra201
Tetra201
A
  • Question Question
VBA to Categorise based on columns
Replies
3
Views
144
Peter_SSs
Peter_SSs
T
  • Question Question
cross country meet scoring
Replies
7
Views
810
Joe4
Joe4
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
269
Cquake
C

Forum statistics

Threads
1,223,108
Messages
6,170,146
Members
452,304
Latest member
Thelingly95

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top