Posts by markc

markc · Aug 11th 2008

Re: Concatenate Each Row To Cells In Column

Kris,

Many thanks! :yourock:
Works great!

Best regards,
marc

markc · Aug 11th 2008

Re: Concatenate Each Row To Cells In Column

Bump.

Hi all,
I've pulled out my hair (what little I had left)
I just can't figure out what I'm doing wrong here.
Any ideas?

See most recent code post above.
Many thanks,
marc

markc · Aug 8th 2008

Re: Concatenate Each Row To Cells In Column

Thanks Dave,

I'm not receiving any more errors.
But I definitely have a logic problem

The final string is correct for the first record (Rows 2-4)
But not the second record (Rows 5-6)

Rows 3 and 4 are updating with comments from rows 5 and 6, respectively
The intent is that rows 2,3,4 should concatentate and be placed in row 2 - which works

Then 6 should be concatenated onto 5 and place in row 5 - this does not work
Apparently I'm not updating intRecord correctly. After buggering with it for 2 hours I"m not sure how to.

Any ideas?
Updated code below

Thanks much!
Regards,
marc

bump
Hi all,
Does anyone have any ideas as to the code logic below
I can't figure out what I'm doing wrong
Thanks!
marc

Code

Option Explicit


Sub wo_Consolidate_Comments()
    Dim wbBook As Workbook
    Dim wsData As Worksheet
    Dim strComment As String
    Dim rngComment As Range
    Dim lngrows As Long
    Dim intCounter As Integer
    Dim intRecord As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    Set wbBook = ThisWorkbook
    Set wsData = wbBook.Worksheets("Data")
    lngrows = wsData.Range("A65536").End(xlUp).Row
    intCounter = 2
    intRecord = intCounter
    strComment = wsData.Cells(intCounter, 5)
    Set rngComment = wsData.Cells(intRecord, 5)
    
    Do While lngrows >= 2
        With wsData
            If .Cells(intCounter + 1, 1) = .Cells(intCounter, 1) Then
                strComment = strComment + " " + .Cells(intCounter + 1, 5).Value
            Else
                Set rngComment = .Cells(intRecord, 5)
                intRecord = intCounter
                rngComment.Value = strComment
                strComment = ""
            End If
            
            intCounter = intCounter + 1
            lngrows = lngrows - 1
        End With
    Loop
    
    Set wbBook = Nothing
    Set wsData = Nothing
    Set rngComment = Nothing
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
     
End Sub

Display More

markc · Aug 8th 2008

Re: Concatenate Each Row To Cells In Column

Thanks Dave,

Code

intCounter = 2
.Cells(intCounter,5) = "71 - Vending Services"

Thanks!
Regards,
marc

markc · Aug 8th 2008

Hi all,

I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set

I believe the code below is close, but I am receiving this error

Quote

Run-time error '91':
Object variable or with block variable not set

Debug points to this line

Code

rngComment = .Cells(intCounter, 5)

I'm not sure what I am doing wrong.
Can you please nudge me in the right direction?
Many thanks!
Regards,
marc

Code

Option Explicit


Sub wo_Consolidate_Comments()
    Dim wbBook As Workbook
    Dim wsData As Worksheet
    Dim strComment As String
    Dim rngComment As Range
    Dim lngrows As Long
    Dim intCounter As Integer
    Dim intRecord As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    Set wbBook = ThisWorkbook
    Set wsData = wbBook.Worksheets("Data")
    lngrows = wsData.Range("A65536").End(xlUp).Row
    intCounter = 2
    intRecord = intCounter
    strComment = wsData.Cells(intCounter, 5)


    With wsData
        Do While lngrows >= 2
            rngComment = .Cells(intCounter, 5)
            
            If .Cells(intCounter, 1) = .Cells(intCounter + 1, 1) Then
                strComment = strComment + .Cells(intCounter + 1, 5)
            Else
                rngComment = strComment
                intRecord = intCounter
            End If
            
            intCounter = intCounter + 1
            lngrows = lngrows - 1
        Loop
    End With
            
        
    
    '//Cleanup
    Set wbBook = Nothing
    Set wsData = Nothing
    Set rngComment = Nothing
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
     
End Sub

Display More

markc · Jul 2nd 2008

Re: Display All Characters In Cell

Thanks guys,

Appreciate the help
I'll have to import to Access

Regards,
marc

markc · Jul 2nd 2008

Hi all
I have entered text as I would like in the formula bar
However not all of the text is displayed in the cell
I have sized the cell so it should definitely dispaly all text
Word wrap is on
But the text displayed in the cell is truncated

Also the spreadsheet was originally in 2007 format, I converted to 2003

I also tried copying all text from the formula bar, pasted to notepad, deleted from Excel, copied from Notepad, pasted back to the formula bar.

Still not all text is displayed
In a helper cell I dropped in =len(xx) where xx is the reference. Returned 1357

Thanks much!
marc

markc · Mar 24th 2008

Re: Microsoft Query: Data Source .xls Files

Thanks Dave,

That solved the problem.

Thanks for your help
Regards,
marc

markc · Mar 23rd 2008

Re: Microsoft Query: Data Source .xls Files

Hi Dave,

Yes, Excel Files is selected in the databases tab of the Choose Source Dialog Box
Still, I do not see any Excel files listed.

I also tried MS Access Database, I browsed to the folder containing the download files
I cannot see .mdb files either.

I tried everything on another PC in the house with only Office 2003 no Office 2007 a in my primary system.

Still, no files are visible when I browse through Choose Data Source in MS Query

Any ideas what is going on?
Thanks
Regards,
marc

markc · Mar 23rd 2008

Hi all -

Trying to work through tutorial on creating offline cube from an Excel file, A CompleteGuide to PivotTables: A Visual Approach, Cornell, ISBN 1-59059-432-0, pg 248.

Steps I am taking:
1. Click on Data Menu
2. Import External Data
3. New Database Query

On Databases tab
4. Click on Excel Files*
5. Click on Browse Button

I migrate to the loction of the sample files I downloaded, but no files appear in the Browse Data Sources dialog window.

Files of type drop down reveals only "Data Sources" (w/o quotes)

I browsed the folder with Windows Explorer. There are several Excel files in the folder as well as sample, .cub and .mdb files.

I do have Excel 2007 installed on the PC as well, but working through the tutroail with Excel 2003.

Any ideas why the Excel files are not visible?
Thanks all
Regards,
marc

markc · Feb 21st 2008

Re: Show Data Labels On Bars Of Bar Chart

Thanks all,

Andy,

That worked great!:music:

Thanks
Regards,
marc

markc · Feb 18th 2008

Re: Show Data Labels On Bars Of Bar Chart

Thanks all,

Dave,
Could you expand a bit?
I tried dragging the vertical value axis to the bar chart, had no impact
Was there somthing else I was to drag n' drop?

Junho,

Thanks. I am trying to make the value axis appear as overlaid on the bar chart as opposed to immediately to the left of the bar chart

Daniel,

Looks good, except lost the data value labels.
Can I have both using this method?

Edit:
I walked through the steps you provided
They were a little different for xl 2007, but very close

For label options I chose Category Name and Value.
For label position, I chose Inside Base

This yields the category name.. a comma.. category value
Is it possible to have the label inside base but the category value to the right end of each bar?

Thanks all!
Regards,
marc

markc · Feb 17th 2008

Hi all,

I would like my y-axis labels to appear on the bar chart intead of next to the chart
Is this possible through chart/axis settings or do I need to manually add text box to each bar and set each text box = the appropriate cell?

The chart is attached
Thanks
Regards,
marc

markc · Feb 17th 2008

Re: Format Dates On Chart Axis

Thanks Andy,
Appreciate your help

Regrds,
marc

markc · Feb 17th 2008

Re: Overlay 2 Charts

Thanks Daniel,

Works great!
Interesting that dragging one chart on top of the other works, but copy/paste does not

Thanks for the help
Regards,
marc

markc · Feb 16th 2008

Hi all,

Using Excel 2007

I am trying to overlay 2 identical charts
On the first chart I set all items to 100% Transparency except for the line and the area charts

On the 2nd chart (exact replica) I did just the oppposite leaving all axes, gridlines, etc.. visible and made the area and line charts transparent

I then right clicked the 2nd chart and brought it forward
I then copied the first chart and pasted it to cell F11

I then copied the 2nd chart and pasted it to cell F11

The outcome was not what I was looking for
I would like the charts to retain all coloring and for the gridlines to appear in front of the line and area charts

What did I do wrong?

Thanks
Regards,
marc

markc · Feb 16th 2008

Re: Format Dates On Chart Axis

Thanks Andy,

I am using Excel 2007
I had to save the workbook in 2003 to upload here.

I set the labels in column F on Data = to Col A for rows 1 & 13.
I then applied a custom date format: [$-409]mmm d, "'"yy (though I would like a 4 letter month to use all letters instead of 3)

I formatted the x axis for text axis, this solved the problem with altering the line chart to end points only.:thanx:

The only item left, is the date labels for points 1 and 13 are wrapping instead of expanding along the x axis.

Is it possible to expand horizontally along the axis or do I need to use a text box and set = Data!$F$1 for the first data point for example?

The workbook is attached
The chart I am working on is in cell H5

Thanks!
Regards,
marc

markc · Feb 15th 2008

Re: Format Dates On Chart Axis

Thanks Dave,

I tried as you suggested.
I used a helper column and only set $F1 and $F13 equal to $A1 and $A13 respectively
This resulted in altering the chart to a straight line instead of the current jagged line.

I also tried Setting $F= $A, which resulted in a jagged line, but too many labels in the x axis

The chart is also displaying the beginning and end dates as Jan 1, '04 and Jan 1, '05 respectively. Should be Feb for both points

The worksheet is attached

Thanks!
Regards,
marc

markc · Feb 15th 2008

Hi all -

Using Excel 2007
Would like to format X-Axis values to display 1st month and 13th month as
Feb 2, '08 for example.

I would like the other 11 tick marks to remain.

Sample attached (Saved in 2003 format, I .xlsx was invalid file type)
4th micro chart uses F1:F13 for values for x axis

Thanks
Regards,
marc

markc · Feb 14th 2008

Re: Calculate Hours Worked Based On 2 Start/End Times

Thanks shg,

The formula returned the exact same result of 8hrs worked as did my formula
Any reason why use your instead since both give the same answer?

Thanks
Regards,
marc