Posts by hgus393

hgus393 · Feb 23rd 2007

Hello all,

I am a newbie to Access and I am struggling to a concatenating two columns together in a table. I am filling in the column that I want to concatenate in the general tab under properties and in the field default value I fill in the two columns that I want to concatenate by the following expression:
[Column1]&[Column2]
Access says that the database engine does not recognize either the field 'Column1' in a validation expression, or the default value in the table 'Tbl_Test'

This is perfect Greek to me, can anyone help?

//Robert

hgus393 · Feb 9th 2007

Re: Values From A Closed Workbook

Hello,

No there is no protection, some of the values are linked in the source file, but that should not be an issue should it?

//Robert

hgus393 · Feb 9th 2007

Hi all,

I wrote here a few days ago about an issue retrieving a closed worksheets values and I got help from RoyUK(see code below and link), but for some reason the values are never entered into the open worksheet. Can anyone understand why, 'cause I can't.

http://www.ozgrid.com/forum/showthread.php?t=63869

Code

Sub FillingSheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
    .GetOpenFilename("XL Files (*.xls), *.xls")
   On Error Resume Next
    Set wb = Workbooks.Open(filetoopen, True, True)
With ThisWorkbook.Worksheets(1)
      .Cells.Value = wb.Worksheets(1).Cells.Value
    End With
    wb.Close False
    Set wb = Nothing
End Sub

Display More

Robert

hgus393 · Feb 6th 2007

Re: Retrieving From A Closed Workbook

Hum worked it out how to get around the error message..thx

hgus393 · Feb 6th 2007

Re: Retrieving From A Closed Workbook

Great that works fine is there a way to get round the error message I get when I press cancel?

hgus393 · Feb 6th 2007

Re: Retrieving From A Closed Workbook

Quote from royUK

Something like

Code

Option Explicit


Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets(1)
        ' read data from the source workbook
        .Range("A1").Value = wb.Worksheets(1).Range("A1").Value
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Display More

That works fine if the workbook to retreive the information from is always in the same place. Can I specify the workbook by

Code

Application.GetOpenFilename

and hard code the name of the sheet?

//Robert

hgus393 · Feb 6th 2007

Re: Retrieving From A Closed Workbook

Quote from StephenR

What do you mean by "retrieve"? Do you want to extract data from a closed workbook?

Yes! I need to retreive data from a worksheet into a master workbook.

//Robert

hgus393 · Feb 6th 2007

Hi all,

I have tried to find how to retrieve a worksheet from a closed workbook and I am almost sure that there was an answer how to do this in FAQ but I cannot seem to find it, does anyone know where this thread is?

//Robert

hgus393 · Feb 2nd 2007

Re: Sum Function Fails

Quote from StephenR

Square brackets in the wrong place I believe:

Thank you thank you thank you!!!!

hgus393 · Feb 2nd 2007

Hi all,
I have a spreadsheet that I am trying to automate, one of the tasks is to sum some columns namely columns O to T. Column B contains the exact number of entries in the columns O to T, therefore I thought that this would be a good counter. The Spreadsheet varies in number of entries every time. In this instance the rows are in total 200 inclusive a header row.
ie r below is = to 200. I have tried to record a macro and from that I have tried to replace the pertinent values. Does any wise person know why the below code would fail?

Code

Sub Sel_Sum ()
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim U As Integer




    Range("B1", Range("B65536").End(xlUp)).Select
    
    r = Selection.Rows.Count
    s = (r - 1) * -1
    t = r + 1
    U = -1
   
   Range("O" & t, "T" & t).Select
   Selection.FormulaR1C1 = "=SUM(R" & [s] & "C:R" & [U] & "C)" 'fails here
   Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    
End Sub

Display More

//Robert

hgus393 · Jan 30th 2007

Re: Vlookup Autofill Fails

Ok,

I admit it i had a brain freeze should have been as follows (Issued solved):

Code

Sub Macro5()


Dim R As Integer


ActiveCell.FormulaR1C1 = "=VLOOKUP(380860,C1:C,COLUMNS(C1:C),FALSE)"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(380860,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(378720,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(390060,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(390590,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(393550,C1:C,COLUMNS(C1:C),FALSE)"


    Range("B1", Range("B65536").End(xlUp)).Select
    R = Selection.Rows.Count
 
        Range("C" & R).AutoFill Range("C" & R, "AG" & R), Type:=xlFillDefault
'Tried to fill in range from selection...
    Range("C" & R).Select


End Sub

Display More

//Robert

hgus393 · Jan 30th 2007

Hi all,

I have tried to get a vlookup formula to work, it works fine until i need to autofill it accross some columns, can anyone see where I am going wrong?

Code

Sub Macro5()


Dim R As Integer


ActiveCell.FormulaR1C1 = "=VLOOKUP(380860,C1:C,COLUMNS(C1:C),FALSE)"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(380860,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(378720,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(390060,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(390590,C1:C,COLUMNS(C1:C),FALSE)+VLOOKUP(393550,C1:C,COLUMNS(C1:C),FALSE)"


    Range("B1", Range("B65536").End(xlUp)).Select
    R = Selection.Rows.Count
    Selection.AutoFill Destination:=Range("C" & R, "AG" & R), Type:=xlFillDefault
    Range("C" & R).Select


End Sub

Display More

//Robert

hgus393 · Jan 25th 2007

Re: Pivot Table Source Information

Quote from thomach

Try using a Dynamic Named Range for your source data. If need be, you could create the Named Range with your code. Then your code can simply refer to this range name (which you could record) and the dyname named range will self adjuct to yoru data source size.

[nr]*[/nr]

Thank you, will give it a whirl.

//Robert

hgus393 · Jan 24th 2007

Re: Applying Mask For Date Entries

Quote from dejayajay

I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of. Help please.

Pls explain what you do, Have you put this in the worksheet?

hgus393 · Jan 24th 2007

Re: Applying Mask For Date Entries

http://www.ozgrid.com/VBA/mask-date-entry.htm

hgus393 · Jan 24th 2007

Hi all,

I have searched the forum for this but I have not found any compatible information. I am trying to automate a tedious task and have found that using a Pivot table is the best way to go. That is all fine, but since I want to automate this, I have tried recording the whole procedure, that works fine too. But, when I tried the macro on some fresh data I saw that some rows were excluded. I have tracked down the culprit and it seems that I have, when I recorded the macro,set a fixed range as the source information. Does anyone know how to get by this? I am sitting on a computer with no Excel (oh my god!!!!) :(, so I cannot post any code. Hope someone understands what I mean.

//Robert

hgus393 · Jan 8th 2007

Re: Between Values In An If Function

Aha,

Thank you all I got it to work with your help.

Thx

Robert

hgus393 · Jan 8th 2007

Hi all,
I am trying to get an [if]*[/if] to work but the function does not work, does anyone know why?

If(NrOfDays<=92;"<=3month";IF(NrOfDays>92<=365;">3month<=1Years";IF(NrOfDays>365<=1826;">1Years<=5Years";IF(NrOfDays>1826<=3652;">5Years<=10Years";IF(NrOfDays>3652;">10Years")))))

Robert

hgus393 · Jan 2nd 2007

Re: Adding Comments In Spreadsheet

Quote from Andy Pope

Simply comment out the code that deals with an existing comment
[vba] If objComment Is Nothing Then
Set objComment = r.AddComment(r.Formula)
Else
'''' objComment.Text r.Formula
End If [/vba]

Thank you Andy!!
Much obliged!!

Cheers

Robert

hgus393 · Jan 2nd 2007

Re: Adding Comments In Spreadsheet

Quote from Andy Pope

[vba]Sub InsertingComments()

Dim r As Range
Dim objComment As Comment

On Error Resume Next
For Each r In Selection
If Len(r.Value) > 0 Then
Set objComment = Nothing
Set objComment = r.Comment
If objComment Is Nothing Then
Set objComment = r.AddComment(r.Formula)
Else
objComment.Text r.Formula
End If
End If
Next r

End Sub[/vba]

Display More

Magic!!

What if I would like to skip a cell in the selection if the cell already has a comment?

//Robert

Posts by hgus393

Primary Keys

Values From A Closed Workbook

Values From A Closed Workbook

Retrieving From A Closed Workbook

Retrieving From A Closed Workbook

Retrieving From A Closed Workbook

Retrieving From A Closed Workbook

Retrieving From A Closed Workbook

Sum Function Fails

Sum Function Fails

Vlookup Autofill Fails

Vlookup Autofill Fails

Pivot Table Source Information

Applying Mask For Date Entries

Applying Mask For Date Entries

Pivot Table Source Information

Multiple If Function Criteria

Multiple If Function Criteria

Add Cell Comments Macro

Add Cell Comments Macro