Friday, September 23, 2016

Solution to Error: Create Records on Another Worksheet

Create Records on Another Worksheet

Ultimately, this macro will be called from another that processes the data from a whole bunch of worksheets.
I have uploaded a sample file
here.

"F2 through F" means beginning with cell F2 and down column F.
For each cell in F2 through
F on Sheet EnterData that contains an X, I need to put a
1 in B2 through B on Sheet PrinterMapSheet, and copy the corresponding ID number (3-digit number) from column O into C2 through C
For each cell in G2 through G on Sheet EnterData that contains an X, I need to put a
2 in B2 through B on Sheet PrinterMapSheet, and copy the corresponding ID number (3-digit number) from column O into C2 through C
For each cell in H2 through H on Sheet EnterData that contains an X, I need to put a
3 in B2 through B on Sheet PrinterMapSheet, and copy the corresponding ID number (3-digit number) from column O into C2 through C
For each cell in I2 through
I on Sheet EnterData that contains an X, I need to put a
4 in B2 through B on Sheet PrinterMapSheet, and copy the corresponding ID number (3-digit number) from column O into C2 through C
For each cell in J2 through
J on Sheet EnterData that contains an X, I need to put a
5 in B2 through B on Sheet PrinterMapSheet, and copy the corresponding ID number (3-digit number) from column O into C2 through C

The number of rows will sometimes be only 2 or 3; will USUALLY be between 200 and 300, but should rarely exceed, say, 1000 or so.

After this, if I could get the code to "autonumber" column A, beginning with 100, that would be great and I would use it in several other areas.
I'm already doing this elsewhere, but I'm using something like below.
If that's not the best method, I'd love
to have a better one.

Range("I2:I" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=IF(ISTEXT(I1),100,IF(H2=H1,I1,I1+1))"
Thanks so much in advance!
(This board has developed my project from a simple worksheet into a full-blown app.
You are all making me look SOOO good, and I thank you for it, and will never be able to repay you.)

Anwsers to the Problem Create Records on Another Worksheet

Download Error Fixer (Free)

How about this...
Public Sub DoStuff()
    Dim rngFoundXs As Range
    Dim rng As Range
    Dim rngPaste As Range
    Dim lngCount As Long
   
    lngCount = 101
    Set rngPaste = Sheets("printermapsheet").Range("B2")
    Set rngFoundXs = FindX("F")
    If Not rngFoundXs Is Nothing Then
        For Each rng In rngFoundXs
            rngPaste.Value = 1
            rngPaste.Offset(0, 1).Value = rng.Parent.Cells(rng.Row, "O").Value
            rngPaste.Offset(0, -1).Value = lngCount
            lngCount = lngCount + 1
            Set rngPaste = rngPaste.Offset(1, 0)
        Next rng
    End If
   
    Set rngFoundXs = FindX("G")
    If Not rngFoundXs Is Nothing Then
        For Each rng In rngFoundXs
            rngPaste.Value = 2
            rngPaste.Offset(0, 1).Value = rng.Parent.Cells(rng.Row, "O").Value
            rngPaste.Offset(0, -1).Value = lngCount
            lngCount = lngCount + 1
            Set rngPaste = rngPaste.Offset(1, 0)
        Next rng
    End If
   
    Set rngFoundXs = FindX("H")
    If Not rngFoundXs Is Nothing Then
        For Each rng In rngFoundXs
            rngPaste.Value = 3
            rngPaste.Offset(0, 1).Value = rng.Parent.Cells(rng.Row, "O").Value
            rngPaste.Offset(0, -1).Value = lngCount
            lngCount = lngCount + 1
            Set rngPaste = rngPaste.Offset(1, 0)
        Next rng
    End If
   
    Set rngFoundXs = FindX("I")
    If Not rngFoundXs Is Nothing Then
        For Each rng In rngFoundXs
            rngPaste.Value = 4
            rngPaste.Offset(0, 1).Value = rng.Parent.Cells(rng.Row, "O").Value
            rngPaste.Offset(0, -1).Value = lngCount
            lngCount = lngCount + 1
            Set rngPaste = rngPaste.Offset(1, 0)
        Next rng
    End If
   
    Set rngFoundXs = FindX("J")
   If Not rngFoundXs Is Nothing Then
        For Each rng In rngFoundXs
            rngPaste.Value = 5
            rngPaste.Offset(0, 1).Value = rng.Parent.Cells(rng.Row, "O").Value
            rngPaste.Offset(0, -1).Value = lngCount
            lngCount = lngCount + 1
            Set rngPaste = rngPaste.Offset(1, 0)
        Next rng
    End If
End Sub
Private Function FindX(ByVal strColumn As String) As Range
    Dim rngToSearch     As Range
    Dim rngFound        As Range
    Dim rngFoundAll     As Range
    Dim strFirstAddress As String
   
    With Sheets("enterdata")
    Set rngToSearch = .Range(.Cells(2, strColumn), .Cells(Rows.Count, strColumn))
    End With
   
    Set rngFound = rngToSearch.Find(What:="X", _
                                    LookAt:=xlWhole, _
                                    LookIn:=xlFormulas, _
                                    MatchCase:=False)
    If rngFound Is Nothing Then
        Set FindX = Nothing
    Else
        Set rngFoundAll = rngFound
        strFirstAddress = rngFound.Address
        Do
            Set rngFoundAll = Union(rngFound, rngFoundAll)
            Set rngFound = rngToSearch.FindNext(rngFound)
        Loop Until rngFound.Address = strFirstAddress
        Set FindX = rngFoundAll
    End If
End Function

Use the System File Checker tool to repair corrupted or missing system files:

  1. Click Start.
  2. Type cmd, and then press CTRL+SHIFT+ENTER to open the elevated command prompt (I.e. Command prompt with administrative rights).
  3. The User Account Control window should appear, be sure that the action it exhibits is correct, and then press the Continue button.
  4. Type or copy paste the below command line into it without quotes:
  5. "Sfc /scannow"

Recommended Method to Fix the Problem: Create Records on Another Worksheet:

How to Fix Create Records on Another Worksheet with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your system.  Open it, and it will perform a scan for your system. The errors will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: How to Fix Error - Copy to end of list?,[Anwsered] Ctrl+t keyboard shortcut does not work to CUT in Paint.,Troubleshooting:corrupted outlook express,Troubleshooter of Error: Crash Report - Shows that Internet Explorer stops working every time it is closed,csrss.exe High Disk Activity,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment