Posts by sneal12

sneal12 · Apr 5th 2016

Re: VBA Vlookup to Changing Workbook Name

This is what I've tried:

ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],[' & workbooks(2) & ']Pivot Tables'!C4:C5,2,FALSE),0)"

sneal12 · Apr 5th 2016

I'm trying to a vlookup in vba between 2 workbooks. The workbook I'm looking up information from will have a different name every day. How do I write the vlookup to reference the current active workbook (the sheet names will always be the same)?

In my other code, I've being using Workbooks(2) as my reference but it doesn't seem to work in the vlookup.

sneal12 · Sep 15th 2015

I've tried this every which way I can think of and I'm having no luck.

I want to make the excel page as easy to use as possible on a touch screen surface. Ideally I could add a page up/page down button on the screen or enlarged scroll arrows.

I tried adding a scroll bar form control but that doesn't work for my needs.

HELP!!

sneal12 · Nov 7th 2014

Hello,
I'm trying to convert one spreadsheet that contains dates across row2 and names down column B. Each person's worked hours are listed across the rows under the date worked.
I want to convert this into lines of data in a new tab which read: name, date, hours worked.
So far I've used VBA to pull all the names and duplicate them for the number of days with hours worked (ie if they've worked 3 days, they will have 3 lines in the new sheet). But I still need to figure out how to return the date and hours worked to the second page.

The first image is the original page. Second is the desired layout.

[TABLE="class: cms_table, width: 708"]

[tr]

[TD="class: cms_table_xl68, width: 64, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl71, width: 44, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Mon[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Tues[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Wed[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Thurs[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Fri[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Sat[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Sun[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Mon[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Tues[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Wed[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Thurs[/TD]
[TD="class: cms_table_xl70, width: 64, bgcolor: transparent"]Fri[/TD]
[TD="class: cms_table_xl70, width: 68, bgcolor: transparent"]Sat[/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl69, bgcolor: transparent"]Name[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]4/28/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]4/29/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]4/30/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]5/1/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]5/2/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]5/3/2014[/TD]
[TD="class: cms_table_xl74, bgcolor: transparent"]5/4/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/5/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/6/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/7/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/8/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/9/2014[/TD]
[TD="class: cms_table_xl76, bgcolor: transparent, align: right"]5/10/2014[/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]3[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Joe[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl77, bgcolor: #DA9694"]2.5[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Bob[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]2[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Sue[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Josh[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]3[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Zach[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]6[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Betty[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"]8[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: cms_table_xl68, bgcolor: transparent, align: right"]4[/TD]
[TD="class: cms_table_xl72, bgcolor: transparent"]Donna[/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl73, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent, align: right"]8[/TD]
[TD="class: cms_table_xl75, bgcolor: transparent"][/TD]

[/tr]

[/TABLE]

[TABLE="class: cms_table, width: 268"]

[tr]

[td][/td]

[td]

Name

[/td]

[td]

Date

[/td]

[td]

Hours Worked

[/td]

[/tr]

[tr]

[TD="align: right"]3[/TD]

[td]

Joe

[/td]

[TD="align: right"]28-Apr[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]

[td]

Joe

[/td]

[TD="align: right"]29-Apr[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]

[td]

Joe

[/td]

[TD="align: right"]30-Apr[/TD]
[TD="align: right"]2.5[/TD]

[/tr]

[tr]

[TD="align: right"]8[/TD]

[td]

Bob

[/td]

[TD="align: right"]2-May[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]8[/TD]

[td]

Bob

[/td]

[TD="align: right"]3-May[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]8[/TD]

[td]

Bob

[/td]

[TD="align: right"]5-May[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]8[/TD]

[td]

Bob

[/td]

[TD="align: right"]4-May[/TD]
[TD="align: right"]8[/TD]

[/tr]

[tr]

[TD="align: right"]8[/TD]

[td]

Bob

[/td]

[TD="align: right"]5-May[/TD]
[TD="align: right"]8[/TD]

[/tr]

[/TABLE]

Posts by sneal12

VBA Vlookup to Changing Workbook Name

VBA Vlookup to Changing Workbook Name

Excel enlarged buttons for page scrolling

VBA to Find all rows with numeric values and return column header