In excel vba I need to have my set range change negatives to positive numbers and negatives to positive.
I can not seem to get it to fire.

Here what I am have been trying:

Private Sub Worksheet_Change(ByVal target As Range)
   
    'On Error Resume Next
         
    If Not Intersect(target, Range("H3:H13")) Is Nothing Then
        Set rng = Intersect(target, Range("H3:H13"))
        For Each r In rng

        Me.Unprotect
       
        Application.EnableEvents = False
       
        target.Value = target.Value / 100
       
     Next r
       
    End If
       
           
    If Not Intersect(target, Range("R3:R13")) Is Nothing Then
      Set rng = Intersect(target, Range("R3:R13"))
        For Each cell In rng
      'Me.Unprotect
     
      'For Each c In target.Cells
          cell.Value = -cell.Value
          cell.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
      Next cell
           
      End If
     
           
      Application.EnableEvents = True
     
    'Me.Protect
   
End Sub

I want to change a text in a function.
I have $A$2 Want to change to $A2.

Thank you,

Is there any way to determine what the current error handling is in vb6?

Default error handling crashes the program displaying the error message.

If you use:

ON ERROR GOTO Label

Select all Open in new window

The program goes to the point in the code with the label and continues executing (presumably, you have defined what to do here to mitigate the error)

If you use:

ON ERROR RESUME NEXT

Select all Open in new window

The program will continue ignoring errors until it either can't run at all, you handle the errors, or you disable the resume next option.  I often (but not always) use ON ERROR RESUME NEXT to allow the program to continue and then I explicitly check for an error number and handle the error there.  For example:

ON ERROR RESUME NEXT
X = 5
IF X = "bob" THEN X = "Bob"
IF ERR.NUMBER <> 0 THEN
   'Handle the error
END IF 

Select all Open in new window

And to disable graceful error handling, you use

ON ERROR GOTO 0

Select all Open in new window

But is there any way to tell if graceful error handling is on?  Any way to tell that the last time an ON ERROR statement executed it was set to go to a label, resume next, or goto 0?

trying to post data from a recordset to sheet.

I have the folloeing recorst created in code:

But trying to loop through the data and place on the worksheet properly

dB2.CommandTimeout = 400
                With rs2
                 .ActiveConnection = dB2
                 .Open strsql
                End With



 
J = rs2.RecordCount

ActiveSheet.Range("A2").Select
      
For i = 1 To J  'rng.Count
    

Do Until rs2.EOF

      
        
        ' place data into sheet
        rng(i).Offset(1, 0) = rs2.Fields(0) '
        rng(i).Offset(1, 1) = rs2.Fields(1) '
        rng(i).Offset(1, 2) = rs2.Fields(2) '
        rng(i).Offset(1, 3) = rs2.Fields(3) '
        rng(i).Offset(1, 4) = rs2.Fields(4) '
        rng(i).Offset(1, 5) = rs2.Fields(5) '
        rng(i).Offset(1, 6) = rs2.Fields(6) '
        rng(i).Offset(1, 7) = rs2.Fields(7) '
        rng(i).Offset(1, 8) = rs2.Fields(8) '
        rng(i).Offset(1, 9) = rs2.Fields(9) '
        rng(i).Offset(1, 10) = rs2.Fields(10) '
        rng(i).Offset(1, 11) = rs2.Fields(11) '
        rng(i).Offset(1, 12) = rs2.Fields(12) '
        rng(i).Offset(1, 13) = rs2.Fields(13) '
        rng(i).Offset(1, 14) = rs2.Fields(14) '
        rng(i).Offset(1, 15) = rs2.Fields(15) '
        rng(i).Offset(1, 16) = rs2.Fields(16) '
     
 

        ActiveCell.Offset(1, 0).Select ' move down to the next row 
       
      rs2.MoveNext
    Loop
 Next i

Select all Open in new window

Thanks
fordraiders

Hello, I need a macro to transposed dates in five columns into one column.

Columns A-H is the raw data
Column J-M are the desired results
If possible, I need the results to only display dates because every record doesn't have dates across all five columns so sum will be blank
The key fields are A,B,C and they need to be displayed on the results, in Order of Pers.no. then ascending in date order
Transpose-Dates-in-many-columns-int.xlsx

I am modifying a procedure for a custom Query By Form. The original procedure searches multiple fields in a table, but I am modifying it to search only single field in a table

The modified code is in the click event of the Search - Test 1 button on frmMainMenu. You can view the original unmodified procedure in the click event of the Search - Test 1 (All Orig. Code).

Here are the text box names:

txtDateFrom -- Date From
txtDateTo -- Date To

Here is the modified code:

' First, validate the dates
    ' If there's something in Date From
    If Not IsNothing(Me.txtDateFrom) Then
        ' First, make sure it's a valid date
        If Not IsDate(Format(Me.txtDateFrom, "mm/dd/yyyy")) Then
            ' Nope, warn them and bail
            MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
            Exit Sub
        End If
        ' Now see if they specified a "to" date
        If Not IsNothing(Me.txtDateTo) Then
            ' First, make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
            End If
            ' Got two dates, now make sure "to" is >= "from"
            If Format(Me.txtDateTo, "mm/dd/yyyy") < _
                Format(Me.txtDateFrom, "mm/dd/yyyy") Then
                MsgBox "Contact To date must be greater 

Select all Open in new window

…

Values to be identified as duplicates will be in the second (B) column (with header). I will need to identify rows with duplicate values in that column, but preserve all values in the other columns of that row.

It would be good to insert the string, "Duplicate" (without quotes) in the column to the far left of the duplicates other than the 1st instance. For the 1st instance, mark as "First Instance", would be desirable. Then I can sort on that column. Non-duplicates should not be marked at all.

Spreadsheet has 75,000 rows.

Using an Inline Function would probably be preferred if possible, rather than a nested VBA loop type macro. But please do what you think best. I'm unable to find a reasonable solution searching the web. -- BTW:  CONDITIONAL FORMATTING hangs the program with too many ROWS.

Sample Content: (sorry, but the "Indent" tags are not working). The point is that the values in other columns of those rows do not affect the logic.
Column A is Blank

Column-B-Title   Column-C-Title   Other-Columns-w-Title
5555 5555 5555 3333 3333 XXXXXX XXXXXX Anything Else
(Note: Sorry about all the edits. Couldn't find a "Preview" button prior to publishing. Not familiar with this new interface.)

Hello,

I have code that works, but it only works if the user enters data into the sub-form. If the user enters data into the form and not the sub-form, the sub-procedure does not run and it exits the form saving the form's data (upon the user clicking Save and close cmd button) without running the sub-procedure which I have all my data validation checks.

Is there a way I can force the form to run the sub-procedure without having the user enter data into the sub-form?

Thanks


The sub-procedure is an "On click" [Event Procedure] with a command button  "Save and Close"

Private Sub cmdSave_Click()

' error checking to make sure all fields are completed
' this was part of RLL's original code
Dim ErrMsg As String
Dim ErrVal As Integer

'By adding a series of Isnull statements, if the sum is not zero then there is a null field
'part of RLL's original code
' we will use this after we check the start and stop dates
ErrVal = IsNull(dvsAIPGroup_Name) + IsNull(dvsRegion_Id) + IsNull(dvsInstrumentType_Id) + IsNull(dvsAIPGroup_StartDate) + IsNull(dvsAIPGroup_StopDate) + IsNull(dvsAIPGroup_CreationDate)


''check to see if we have any dates that are in the wrong order.
Dim sql As String
Dim rs As DAO.Recordset
Dim i As Integer
i = 0
On Error GoTo ErrorHandler

' This sets the query def here. Pulls the data into our passthrough query
CurrentDb.QueryDefs("ptqry_CheckDates").sql = "SELECT dvsAIPGroupTarget_ID, dvsAIPGroup_Id" & _
" ,dvsAIPGroupTarget_StartDate, 

Select all Open in new window

…

trying to update a record in sql server
getting error message when a field is blank  ( = null)


These are unbound  textboxes on a form.

R.Edit
' 13 FIELDS SKU INFORMATION
      R![SKU] = Nz(Me("SKU_FRM"), "") ' SKU
      R![QTY] = Nz(Me("QTY_FRM"), "") ' QTY
      R![TARGET_PRICE] = Nz(Me("TARGET_PRICE_frm"), "") ' TARGET_PRICE
      R![TARGET_GP] = Nz(Me("TARGET_GP_FRM"), "") ' TARGET_GP
      R![CURRENT_PRICE] = Nz(Me("CURRENT_PRICE_FRM"), "") ' CURRENT PRICE
      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), "") 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), "") ' FLOOR_GP
      R![PRODUCT_DESCRIPTION] = Nz(Me("PRODUCT_DESCRIPTION_FRM"), "") 'PRODUCT_DESCRIPTION
      R![TARIFF_ITEM] = Nz(Me("TARIFF_ITEM_FRM"), "") '  TARIFF ITEM
      R![RENEWAL_SKU] = Nz(Me("RENEWAL_SKU_FRM"), "")  '  RENEWAL SKU
      R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "") '  RENEWAL OLVP ITEM
      R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "") ' APPROVED PRICE                                                                             error
      R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "") ' APROVED GP                                                                                               error

R.Update

Select all Open in new window

i thought  NZ would take care of a blank value ?


These 2 fields may not have data in the textbox,
but i still need the code to finish the .update

Thanks
fordraiders

echo %username% and environ("%username%") are not working.  

This morning echo %username% and environ("%username%") stopped working.  
It affected all vba programs and batch files in my windows 10 (1903 build 18362.418) laptop.

The following screen shots show the problem.

I am working from home using windows 10 "always on vpn" to connect to a Microsoft Server Essentials 2016 domain.  
I suppose it is time for yet another reboot, but I have a lot of stuff open.  Does anybody have an idea of what I might try?


Background:

Saturday evening everything was fine and I went to bed without closing several Explorer folders.
While I slept, the connection got lost so on Sunday morning the Explorer windows showed "the directory name is invalid".

I reconnected, and continued to work with no obvious problems.
But, about a half hour later one of my batch files failed because %username% was not working.  

My documentation of the problem is shown in the above screen shots.

Hi all, i have 2 unbound text boxes costA and CostB and 1 bound text box rate in continuous form. now i am writing vba code to get rate value depending on costa and costb values.
if costa and costb values are null then rate = 0
if costa has value then rate = costa or
if costb has value then rate = costb
please note that in continuous form we will be having either costa or costb value. we don't have both values same time. one value will be always null
whatever code i am trying to modify it is either working for costa or costb but not for both.
  If (IsNull(CostB) = True And IsNull(CostA) = True) Then
   
        Rate.Value = 0
     ElseIf (IsNull(CostB) = False And IsNull(CostA) = True) Then
     
        Rate.Value = CostB.Value
        Else
        Rate.Value = CostA.Value
     
        End If

can someone tell this is happening because of continuous form?

Hi

I have a SQL statement that I want to load data to a grid with in Access. What is the best way to do this
My statement is    strSQL = "SELECT * FROM " & T & " WHERE FirearmFK = " & FirearmPK

Experts,

I have an excel spreadsheet that will have data align horizontally.  I would like to have this automated and by clicking a button the data will convert to a vertical format, maintain all formulas, formatting and copied to a new tab sheet.  the input of data needs to be done in a horizontal format, but creating pivot tables need to be done with the data in a vertical format.

I know how to convert using the transpose in excel but that will take a lot manual effort and it doesn't always transpose correctly.

I am attaching a file of the horizontal format, but data and formulas have not been added.