Automatizar una macro y pegar un bucle de 1 a 10 mil

Tengo una macro que busca coincidencias

Pon aquí tu macro, utiliza el icono para insertar código.

En "sheet 1" D2 insertar bucle y cuando la búsqueda este en 100% o lo más cerca, se detenga.

Explica aquí con imágenes lo que deseas. Lo que debe contener una imagen:

Puedes poner más de una imagen si lo requiere tu explicación.

Sub BuscarDatos()
  Dim a As Variant, b As Variant
  Dim dic As Object, i As Long
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("A1:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row).Value2
  b = Sheets("Sheet2").Range("A1:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(3).Row).Value2
  ReDim c(1 To UBound(b), 1 To 2)
  '
  For i = 1 To UBound(a, 1)
    dic(a(i, 1)) = a(i, 2)
  Next
  '
  For i = 1 To UBound(b, 1)
    c(i, 1) = b(i, 1)
    c(i, 2) = dic(b(i, 1))
  Next
  '
  Sheets("Sheet3").Range("A1").Resize(UBound(c, 1), 2).Value = c
End Sub

Lo mejor es poder enviarte archivo excel..gracias

Pero no pusiste ninguna explicación.

Podrías intentar explicar con tu imagen lo que necesitas.

Es importante que pongas el resultado que tú deseas.

¡Gracias! En la " hoja 1 celda D2 " se deben generar números entre 1 y 10000 mil, hasta encontrar en la "hoja 1 celda E2" la coincidencia con la celda E3 (20) ö lo más cercano al numero 20 .En este caso se encontró el numero " 9 ". Gracias

En la celda E9 y E3 tienes fórmulas.

Puedes poner aquí las fórmulas.

¿Y la macro que pusiste para qué es?

¿Tiene algo que ver con encontrar un número cercano a 20?

E9 tiene enlace a U3 hoja partidoapartido y E3 viene de enlace L26 hoja1... la macro es para buscar números generados en partidoapartido y devuelve el numero en sheet1 columna B.. tengo una secuencia de números en columna DE hoja1 (20 números) y busco su similar en columna J hoja1 ... los números entre 1 y 10 mil, me permiten hacer ingeniería inversa para encontrar el algoritmo que me simule la secuencia de la columna DE hoja1

¿Y la macro que pusiste para qué es?

¿Tiene algo que ver con encontrar un número cercano a 20?

Más explicaciones... al ingresar el numero 852 o 920 u otro numero; se generan números en hoja partidoapartido V3;V10;V13;V16;V19 etc y la "macro" busca su correspondiente en sheet1 columna B y pega estos valores en hoja1 columna F y J donde posteriormente compara esta secuencia de números con la existente en columna D (20 números)... y da como resultado el % de acierto ... gracias

Revisa si esto es lo que necesitas:

Sub Encontrar_Coincidencia()
  Dim sh As Worksheet, i As Long
  Dim min As Double, t1 As Double, n As Double
  Dim t As Double
  '
  Application.ScreenUpdating = False
  Application.StatusBar = False
  '
  t = Timer
  Set sh = Sheets("Sheet1")
  min = 9.9 ^ 307
  For i = 1 To 10000
    Application.StatusBar = "Procesando el número: " & i
    sh.Range("D2").Value = i
    Call BuscarDatos
    If Not IsError(sh.Range("E2")) Then
      n = sh.Range("E3").Value - Range("E2").Value
      If n < min Then
        t1 = Range("D2").Value
        min = n
      End If
      DoEvents
    End If
  Next
  '
  If t1 > 0 Then
    Range("D2").Value = t1
    Call BuscarDatos
  End If
  '
  Application.ScreenUpdating = True
  Application.StatusBar = False
  '
  MsgBox "Terminado en : " & Format(Timer - t, "0.00 secs")
End Sub
Sub BuscarDatos()
  Dim a As Variant, b As Variant
  Dim dic As Object, i As Long
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("A1:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row).Value2
  b = Sheets("Sheet2").Range("A1:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(3).Row).Value2
  ReDim c(1 To UBound(b), 1 To 2)
  '
  For i = 1 To UBound(a, 1)
    dic(a(i, 1)) = a(i, 2)
  Next
  '
  For i = 1 To UBound(b, 1)
    c(i, 1) = b(i, 1)
    c(i, 2) = dic(b(i, 1))
  Next
  '
  Sheets("Sheet3"). Range("A1").Resize(UBound(c, 1), 2).Value = c
End Sub

Para 10,000 repeticiones las macros tardaron 136 segundos.

Y el número más cercano a 20 fue el 16. El número 16 se calculó con el número 86.

Prueba y me comentas.

Hola como puedo modificar la columna B sheet 1.. aparecen números de 4 dígitos y quiero cambiarlos por números de 3 dígitos ... gracias

Como puedo modificar la columna B sheet 1.. aparecen números de 4 dígitos y quiero cambiarlos por números de 3 dígitos

No entiendo a qué te refieres.

Por cierto, ¿La macro funcionó?

La macro funciona de maravilla, pero al hacer un análisis más profundo .Solo necesito en columna B sheet1 números de 3 dígitos .. y al cambiarlos me muestra error la macro.. gracias

Hola .. al aumentar la base de datos a 100 mil el tiempo de ejecución aumenta (mi pc tarda mucho más que el tuyo).. que se puede hacer para disminuir el tiempo de ejecución.. gracias

Al aumentar la base de datos a 100 mil el tiempo de ejecución aumenta (mi pc tarda mucho más que el tuyo).. que se puede hacer para disminuir el tiempo de ejecución.

Para disminuir el tiempo tendrías que hacer en MEMORIA los cálculos de TODAS las fórmulas de TODAS las hojas.

Hola ..que significa "hacer en memoria". gracias

Según puedo ver en tus hojas tienes fórmulas que cambian cada vez que pones un valor en la celda D2.

Los procesos de lectura y escritura en las celdas, son más lentos que si lo haces en memoria.

Si quieres disminuir los tiempos entonces debes olvidar todas las fórmulas que tienes en las hojas y hacer todos esos cálculos dentro de la macro.

Te voy a poner un ejemplo muy básico para explicar el punto de "hacer en memoria"

En la celda C2 tienes la fórmula:

=A2 * B2

Si modificas la celda A2, entonces excel recalcula: lee la celda A2, lee la celda B2, multiplica la celda A2 * B2 y escribe en la celda C2. Lo que hizo excel fue realizar 2 procesos de lectura y uno de escritura.

Si multiplicas esto por 100,000 celdas entonces tendrás 200,000 procesos de lectura y 100,000 procesos de escritura. A eso le llamamos procesos de Lectura/Escritura (I/O Input/Output). Eso hace que lo procesos sean lentos.

Para hacer el proceso en memoria, lees la celda A2 y B2 (al mismo tiempo), multiplicas en memoria A2 * B2 y escribes el resultado en C2. Parece ser que es lo mismo. Pero si lo realizas en 100,000 celdas. La macro solamente realizará una lectura de A2 hasta B100, 000, procesará en memoria todas las multiplicaciones. Al final escribirá solamente una vez. Lo que te quiero decir, es que esta manera de programar realizará solamente 1 lectura y 1 escritura, en total 2 procesos, mientras que hacer los cálculos en las celdas, tendrás 200,000 de lectura y 100,000 de escritura, en total 300,000 procesos.

Me tomare un tiempo en digerirlo ..gracias

Hola .. la macro que me enviaste ha funcionado muy bien ... hasta que modifique el ciclo for i=1 to10000... en "buscar coincidencia" ... donde no me da la intentar de "D2" en "sheet1"... solo me da el valor de 1 o el valor de 10000... he incluso ahora sin modificar no me da la intentar.. cuando puedas veelo y me ayudas ... gracias