Comments by rawilken - CodeProject

rawilken 16-Oct-13 10:50am View

No.

rawilken 24-Sep-13 11:14am View

Deleted

The problem to solve is from the AfterColUpdate event, take the value that is another column called qty in the grid and perform calculations with it. What I wqas doing was converting the elements of the grid into a datatable in memory with the idea of going to the row that was changed and performing some analysis and calcs.

Collapse | Copy CodePrivate Sub tdbFails_AfterColUpdate(ByVal sender As System.Object, ByVal e As C1.Win.C1TrueDBGrid.ColEventArgs) Handles tdbFails.AfterColUpdate
dt = DirectCast(tdbFails.DataSource, DataTable)
For i As Integer = 0 To dt.Rows.Count - 1
If dt.Rows(i) = tdbFails.Then Then (locate the row that was changed)

If dt.Rows(i)("Include") = True Then
IsNoSegCheckACat = GTAH.CheckIsNoSegCheckACAT(dt.Rows(i).Item("AcctNo"), mtblNoSegCheckAcats)
If (dt.Rows(i).Item("RDCode").ToUpper = "D") Then
'121212 - checking if not an acat before Adding to TotalNonACATDel
If Not IsNoSegCheckACat Then
Me.SEG.SegItem.SendToQueueNonAcat += ReplaceEmptyString(dt.rows(i).Item("Qty"))
Else
totalAcatDel += ReplaceEmptyString(dt.Rows(i).Item("Qty"))

rawilken 24-Sep-13 10:45am View

You are correct. My oversight. I need to use the Qty column from the row that was clicked on the change event. How do I get to it in the data table?

rawilken 17-Dec-12 18:24pm View

Still have Paste method failed errors.
My code is now...
Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
' then copy the formulas from the line above into the new line
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim strFound As String
Dim strSection As String
Dim intWorkSht As Integer

strFound = Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Do While strFound <> vbNullString
' Move from column F to column D to get the name of the Section for the New Account
ActiveCell.Offset(0, -2).Select
strSection = ActiveCell.Value

' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Move from column D to column A to get the Account Number
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, -3).Select
Selection.Copy

' Move to the column B and paste the copied Account Number
Sheets("Tax Basis BS-SL").Select
ActiveCell.Offset(0, 1).Select
Range("B" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

' Go back to the Trial Balance and get a copy of the Account Name from column C
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy

' Return to the Tax Basis BS and paste the Account Name
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Select
Range("A" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

' Go to column c at the row above and copy all the formulas in that row to the added row
ActiveCell.Offset(-1, 2).Select
Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).PasteSpecial Paste:=xlPasteValues

intWorkSht = 1

Do While intWorkSht < 5
' Go to the next Tax Basis BS worksheet and append a new row in the correct section
If intWorkSht = 1 Then
Sheets("Tax Basis BS-West").Select
ElseIf intWorkSht = 2 Then
Sheets("Tax Basis BS-Other").Select
ElseIf intWorkSht = 3 Then
Sheets("Tax Basis BS-VIE").Select
ElseIf intWorkSht = 4 Then
Sheets("Tax Basis BS-East").Select
End If
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Return to the Tax Basis BS SL and copy the A

rawilken 17-Dec-12 15:37pm View

Newly revised code. Problem still occurs. Paste fails! Need help adding Do loop to repeat for each entry of NEW_ACCT in column F of East Trail Balance.

Code...

Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim strSection As String
Dim intRowA As String
Dim intRowB As String
Dim intWorkSht As Integer

' Go to Trial Balance and search for a New Account
Sheets("East Trial Balance").Select
Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

' Move from column F to column D to get the name of the Section for the New Account
ActiveCell.Offset(0, -2).Select
strSection = ActiveCell.Value

' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
intRowA = ActiveCell.Row
intRowB = ActiveCell.Row
Range(intRowA & ":" & intRowB).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Move from column D to column A to get the Account Number
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, -3).Select
Application.CutCopyMode = False
Selection.Copy

' Move to the column B and paste the copied Account Number
Sheets("Tax Basis BS-SL").Select
ActiveCell.Offset(0, 1).Select
intRowB = "B" & ActiveCell.Row
Application.CutCopyMode = False
Range(intRowB).PasteSpecial Paste:=xlPasteValues

' Go back to the Trial Balance and get a copy of the Account Name from column C
Sheets("East Trial Balance").Select
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Select
Selection.Copy

' Return to the Tax Basis BS and paste the Account Name
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Select
intRowA = "A" & ActiveCell.Row
Application.CutCopyMode = False
Range(intRowA).PasteSpecial Paste:=xlPasteValues

' Go to column c at the row above and copy all the formulas in that row to the added row
ActiveCell.Offset(-1, 2).Select
intRowA = "C" & ActiveCell.Row
Range(intRowA).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
intRowA = "C" & ActiveCell.Row
Range(intRowA).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

intWorkSht = 1

Do While intWorkSht < 5
' Go to the next Tax Basis BS worksheet and append a new row in the correct section
If intWorkSht = 1 Then
Sheets("Tax Basis BS-West").Select
ElseIf intWorkSht = 2 Then
Sheets("Tax Basis BS-Other").Select
ElseIf intWorkSht = 3 Then
Sheets("Tax Basis BS-VIE").Select
ElseIf intWorkSht = 4 Then
Sheets("Tax Basis BS-East").Select
End If
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
intRowA = ActiveCell.Row
intRowB = ActiveCell.Row
Range(intRowA &

Comments by rawilken (Top 5 by date)