Апостроф в результате работы макроса

Автор Konstanta, 25 октября 2019, 16:57

0 Пользователи и 1 гость просматривают эту тему.

Konstanta

Приветствую всех!

Есть макрос, который большой массив формул копирует и вставляет как значения:

Range("H3:O20000").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

Все ячейки отформатированы как число.
Результат формул тоже числа.
Но после выполнения макроса он все числа вставляет как текст, в каждую ячейку добавив апостроф перед значением.
Подскажите пожалуйста, как изменить код, чтобы он вставлял данные именно как числа?
Спасибо!

economist

Форматирование ячейки - не меняет её содержимого, и того что копируется макросом. Но может влиять на реализацию VBA в SB.
В первую очередь я бы убедился, что в ячейках именно числа. Самое простое: протянуть выделение мышью - ячейки должны суммироваться в строке состояния.

Если что=то копировали извне, скажем из 1С - то формат чисел м.б. особый (разделители разрядов-триад, десятичная точка/зпт), вот Calc и посчитает это не числом, а текстом, и вставит его с '
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Konstanta

#2
Значения ячеек копируются не из вне.
В данных ячейках макрос вводит формулы и вставляет значения в эти же ячейки.
До запуска макроса все ячейки вычисляются и содержат числа, которые участвуют в вычислении других результатов.
Но множество строк с формулами сильно замедляет работу приложения.
Так как для дальнейшей работы сами формулы уже не нужны, я копирую эти ячейки и вставляю как значения.
Но после выполнения макроса те числа которые до его выполнения были таки числами, становятся текстом поскольку в ячейке появляется апостроф.
Естественно что их перестают распознавать формулы.


economist

До за пуска макроса, при протягивании мышью - ячейки суммируются в строке состояния?
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Konstanta


mikekaganski

Приложите пример (ODS) таблицы с макросом для тестирования.
С уважением,
Михаил Каганский

Konstanta

#6
Файл приложил:
в столбцах J:N
значения с апострофом
но тоже странно:
в ячейке J3 значение как число а уже с ячейки J4 и далее идёт с апострофом.
Неоднородно как-то

mikekaganski

И какой из макросов должен где отработать? Пожалуйста, учитывайте, что покрытие телепатией ещё не тотальное ;)
С уважением,
Михаил Каганский

Konstanta

:)

Sub Формулы_Общий разбит на этапы:
1. заполняет строку 3 формулами;
2. закомментирован как "Очистить_Общий Макрос" очищает от содержимого все поля для последующего копирования.
3. 4 закомментированы как " Индекс1 Макрос"  "Индекс2 Макрос" выполняют копирование строки с формулами до конца таблицы

5.  "Значения1 Макрос" копирует диапазоны ячеек и вставляет их как значения.
вот собственно он и выдаёт апострофы в ячейках


mikekaganski

У меня на 6.3.3.2 всё отрабатывает, и после завершения макроса везде в диапазоне J3:N49 нули в виде нормальных чисел.

Постарайтесь сделать пример, в котором не будет лишнего, и в котором будет только пара строк и столбцов с готовыми формулами, а единственный макрос будет только копировать и вставлять, и после этого можно будет наблюдать проблему в этих нескольких ячейках.
С уважением,
Михаил Каганский

Konstanta

Я тогда понял в чём проблема
Если у Вас нули в виде чисел и нормально работают, значит у меня в обновляемых связях подтянуты данные как текст.
Я у себя исправлю формулы чтобы выдавали значения "=значен" и проверю.
Спасибо за мысль!

mikekaganski

А вообще формулы слишком громоздкие (даже не принимая в расчёт, что Вы продолжаете использовать VBA с FormulaR1C1, когда известно, что FormulaR1C1 имеет баг с неправильным использованием ";" вместо ",", что будет исправлено рано или поздно, и Вам придётся всё переделывать).

Например,
ActiveCell.FormulaR1C1 = _
        "=INDEX('Мат ведомость'!C1:C22;MATCH(RC1;'Мат ведомость'!C1;0);R1C)"


в результате INDEX работает с диапазоном из 22 столбцов, и возвращает одну ячейку из выбранной строки и фиксированного столбца - вместо изначальной передачи туда диапазона с одним столбцом:

ActiveCell.FormulaR1C1 = _
        "=INDEX('Мат ведомость'!C" & Range("H1").Value & ";MATCH(RC1;'Мат ведомость'!C1;0))"


Не смотрел в подробности реализации INDEX, но может быть значительное ускорение.
Ну, и уж конечно не говоря о том, что эта конструкция проще реализуется с помощью VLOOKUP:

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1;'Мат ведомость'!C1:C" & Range("H1").Value & ";" & Range("H1").Value & ";0)"

С уважением,
Михаил Каганский

Konstanta

mikekaganski задача решена, с Вашей помощью!
Действительно, у меня подтянуты были значения в текстовом формате из связи.
Принимая во внимание, что эти же данные (как текст) будут и в будущем выгружаться, я просто применил "= значен" к вычислению. 
Совместив это с предложенной Вами ВПР (она же VLOOKUP)  получил в итоге=

ActiveCell.FormulaR1C1 = _
        "=VALUE(VLOOKUP(RC1;'Мат ведомость'!C1:C" & Range("J1").Value & ";" & Range("J1").Value & ";0))"
И в результате все данные как числа.
ещё раз Спасибо!
задача решена!

economist

Цитата: Konstanta от 28 октября 2019, 10:57у меня подтянуты были значения в текстовом формате из связи.
Задача была бы решена быстрее, если бы вы честно и правильно ответили на два моих вопроса о автосуммировании ячеек в строке состояния (при выделении и протягивании их мышью). Т.к. там были не-числа, вы не обратили внимание что при расширении выделения - какие-то ячейки с "числами" не увеличивали сумму.

Теперь по существу внезапного появления смеси "числа-текст" в Calc и Excel и их связи с "форматом ячейки". Я сам грешу VBA в Calc, и согласен со старшими товарищами что это нехорошо и требует проверок и осторожности. Экселевские привычки надо искоренять. Excel чудит почище чем Calc, он сам тайком меняет формат ячейки с Общий на Текстовый, если изменили формат ячейки-источника на Текстовый. Calc же хранит формат "до последнего" и сам его на Текстовый с числового, даты итд - не меняет. Если внешние файлы проходили через Excel - лучше всё делать через функции =ЗНАЧЕН/VALUE, если ожидается число.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Konstanta

Цитата: economist от 28 октября 2019, 13:43Задача была бы решена быстрее, если бы вы честно и правильно ответили на два моих вопроса о автосуммировании ячеек в строке состояния (при выделении и протягивании их мышью). Т.к. там были не-числа, вы не обратили внимание что при расширении выделения - какие-то ячейки с "числами" не увеличивали сумму.
Дело в том, что по "экселевской привычке" я смотрел не в строку состояния, а в результат вычислений.
То есть пока в ячейках были формулы, то и и в итоговой формуле, ссылающейся на данный диапазон, были верные результаты вычислений.
А вот когда были вставлены значения, итоговая формула выдала "знач"