Simple macro placing value in cell based on values in other cells

J

jpenndcl

New Member
Joined
Aug 14, 2008
Messages
19
I have a list of data in four columns (multiple rows), e.g.,

1 2 3 4


I inserted 2 additional columns after the data to categorize the data according to specific customer feedback, e.g.,

5 6


I am trying to create a macro that takes the original 4 columns of data, copies it into a second worksheet, inserts a column between column 1 and 2 (lets call it column 1.2 for now), and fills column 1.2 in based on the data in column 5 and 6. What I mean by the "filling in column 1.2" is that I would like for the macro to see what the value is in the first row in column 5 and column 6, go to a specific area on a third worksheet based on the combined information in column 5 and 6, row 1, see what the value is in that specific location, and put that value in row 1 of column 1.2 in worksheet 2

I would like the process to repeat as long as there is a data value (other than 0) in column 1. Can anyone help?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
stanleydgromjr said:
jpenndcl ,

Screenshots of your worksheets would help.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan
Click to expand...

The program wouldn't let me download it, but perhaps I can put some more illustrative examples in here

(Data tab)

<table x:str="" style="border-collapse: collapse;" width="466" border="0" cellpadding="0" cellspacing="0" height="67"><col style="width: 48pt;" span="4" width="64"> <col style="width: 58pt;" width="77"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" x:num="37257" width="64" align="right" height="17">1/1/2002</td> <td class="xl23" style="width: 48pt;" x:num="0.41666666666666669" width="64" align="right"> 10:00
</td> <td style="width: 48pt;" width="64"> bob</td> <td valign="top">
</td><td valign="top">
</td><td style="width: 48pt;" x:num="" width="64" align="right">123
</td> <td style="width: 58pt;" width="77"> s</td> <td style="width: 48pt;" x:num="" width="64" align="right">1</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="39451" align="right" height="17">1/4/2008</td> <td class="xl23" x:num="0.5" align="right">12:00
</td> <td> anne</td> <td valign="top">
</td><td valign="top">
</td><td x:num="" align="right">214</td> <td> s</td> <td x:num="" align="right">3</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="38780" align="right" height="17">3/4/2006</td> <td class="xl23" x:num="0.25" align="right">6:00
</td> <td> jeff</td> <td valign="top">
</td><td valign="top">
</td><td x:num="" align="right">231</td> <td> s</td> <td x:num="" align="right">3</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="37843" align="right" height="17">8/10/2003</td> <td class="xl23" x:num="0.64583333333333337" align="right">3:30
</td> <td> dale</td> <td valign="top">
</td><td valign="top">
</td><td x:num="" align="right">421</td> <td> s</td> <td x:num="" align="right">2</td> <td>
</td> </tr> </tbody></table>

(Data to be inserted in library tab)

<table x:str="" style="border-collapse: collapse; width: 240pt;" width="320" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" x:num="37257" width="64" align="right" height="17">1/1/2002</td> <td class="xl24" style="width: 48pt;" width="64"> home</td> <td class="xl25" style="width: 48pt;" x:num="0.41666666666666669" width="64" align="right">10:00
</td> <td style="width: 48pt;" width="64"> bob</td> <td style="width: 48pt;" x:num="" width="64" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="39451" align="right" height="17">1/4/2008</td> <td class="xl24" x:str="lunch "> lunch </td> <td class="xl25" x:num="0.5" align="right">12:00
</td> <td> anne</td> <td x:num="" align="right">214</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="38780" align="right" height="17">3/4/2006</td> <td class="xl24" x:str="lunch "> lunch </td> <td class="xl25" x:num="0.25" align="right">6:00
</td> <td> jeff</td> <td x:num="" align="right">231</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="37843" align="right" height="17">8/10/2003</td> <td class="xl24"> variable</td> <td class="xl25" x:num="0.64583333333333337" align="right">3:30
</td> <td> dale</td> <td x:num="" align="right">421</td> </tr> </tbody></table>

(categories tab)

<table x:str="" style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">home</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="variable " height="17">variable </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">lunch</td> </tr> </tbody></table>

(library tab... not part of the macro, but the end result after I insert the categorized data from the "data to be inserted into library" tab at the end of the already existing library)

<table x:str="" style="border-collapse: collapse; width: 240pt;" width="320" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">Date</td> <td style="width: 48pt;" width="64">Category</td> <td style="width: 48pt;" width="64">Time</td> <td style="width: 48pt;" width="64">Name</td> <td style="width: 48pt;" width="64">Number</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="37257" align="right" height="17">1/1/2002</td> <td class="xl24"> home</td> <td class="xl25" x:num="0.41666666666666669" align="right">10:00
</td> <td> bob</td> <td x:num="" align="right">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="39451" align="right" height="17">1/4/2008
</td> <td class="xl24" x:str="lunch "> lunch </td> <td class="xl25" x:num="0.5" align="right">12:00
</td> <td> anne</td> <td x:num="" align="right">214</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="38780" align="right" height="17">3/4/2006</td> <td class="xl24" x:str="lunch "> lunch </td> <td class="xl25" x:num="0.25" align="right">6:00
</td> <td> jeff</td> <td x:num="" align="right">231</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="37843" align="right" height="17">8/10/2003</td> <td class="xl24"> variable</td> <td class="xl25" x:num="0.64583333333333337" align="right">3:30
</td> <td> dale</td> <td x:num="" align="right">421</td> </tr> </tbody></table>
 
Upvote 0
Upvote 0
Data to be sorted/categorized tab

Excel Workbook
ABCDEF
11/1/200210:00bob123s1
21/4/200812:00anne214s3
33/4/20066:00jeff231s3
48/10/20033:30dale421s2
Categorized data


Data after being sorted tab - ready to insert into table

Excel Workbook
ABCDE
11/1/2002home10:00bob123
21/4/2008lunch12:00anne214
33/4/2006lunch6:00jeff231
48/10/2003variable3:30dale421
Data to insert into Library


Category tab

Excel Workbook
A
1home
2variable
3lunch
Categories
 
Upvote 0
jpenndcl,

Here you go.

Sample worksheets:

Excel Workbook
ABCDE
1
2
3
4
5
6
Library



Excel Workbook
A
1home
2variable
3lunch
Categories



Excel Workbook
ABCDEF
11/1/200210:00bob123s1
21/4/200812:00anne214s3
33/4/20066:00jeff231s3
48/10/20033:30dale421s2
Data



After the "MoveData" macro:

Excel Workbook
ABCDE
1
21/1/2002home10:00bob123
31/4/2008lunch12:00anne214
43/4/2006lunch6:00jeff231
58/10/2003variable3:30dale421
6
Library




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).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code: 
Option Explicit
Option Base 1
Sub MoveData()
    Dim CatArray As Variant
    Dim MyCount&, DLR&, CLR&, LLR&, Ctr&, DCtr&, LCtr&
    Application.ScreenUpdating = False
    With Sheets("Library")
        With Sheets("Categories")
            MyCount& = .Cells(Rows.Count, "A").End(xlUp).Row
            ReDim CatArray(MyCount)
            For Ctr& = 1 To MyCount& Step 1
                CatArray(Ctr&) = .Cells(Ctr&, "A").Value
            Next Ctr
        End With
        DLR& = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
        With Sheets("Data")
            For DCtr& = 1 To DLR& Step 1
                LLR& = Sheets("Library").Cells(Rows.Count, "A").End(xlUp).Row + 1
                .Range("A" & DCtr&).Copy Sheets("Library").Range("A" & LLR&)
                .Range("B" & DCtr& & ":D" & DCtr&).Copy Sheets("Library").Range("C" & LLR&)
                Sheets("Library").Range("B" & LLR&) = CatArray(.Range("F" & DCtr&))
            Next DCtr&
        End With
        With .Range("B1:B" & LLR& + 1)
            .HorizontalAlignment = xlCenter
        End With
        .Range("A1:E" & LLR&).Columns.AutoFit
        .Range("F1").Select
    End With
    Application.ScreenUpdating = True
End Sub


Then run the "MoveData" macro.


Have a great day,
Stan
 
Upvote 0
try
Change bold part(sheet names) to actual sheet names.
Rich (BB code): 
Sub test()
With Sheets("Data to insert into Library")
    Sheets("Categorized data").Range("a1").CurrentRegion.Copy .Range("a1")
    With .Range("a1").CurrentRegion
        .Columns("b").Insert xlShiftToRight
        With .Columns("b")
            .formula = "=vlookup(g1,Categories!a:b,2,false)"
            .Value = .Value
        End With
    End With
    .Columns("f:g").Delete
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
If statement to return list of values based on 1 criteria
Replies
3
Views
165
Peter_SSs
Peter_SSs
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
204
mumps
mumps
M
  • Question
large/complicated =(Sort(Unique)) or similar
Replies
1
Views
164
KRice
K
W
  • Question
I need a Formula to Autofill the date based on the Date, Specific Value, and then no of days
Replies
6
Views
200
Alex Blakenburg
A
F
  • Question
VBA to take a field value from 1 worksheet and update that field value to another worksheet based on another cell value
Replies
4
Views
194
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,726
Messages
6,126,498
Members
449,316
Latest member
sravya

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